0

I have a large table:

+----+-----------+------------+--------+--------+--------+
| Id | lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+----+-----------+------------+--------+--------+--------+
|  0 |         5 | 10/10/2020 |        |        |      9 |
|  1 |         5 | 10/11/2020 |      2 |        |        |
|  2 |         5 | 10/12/2020 |      2 |        |        |
|  3 |         5 | 10/12/2020 |      3 |     10 |        |
|  4 |         6 | 10/13/2020 |      3 |     10 |      8 |
|  5 |         6 | 10/29/2020 |        |     11 |        |
|  6 |         6 | 10/15/2020 |      9 |     13 |        |
|  7 |         6 | 10/16/2020 |        |        |     16 |
+----+-----------+------------+--------+--------+--------+

I need to extract each prop for each clients (lProp1 - lProp3 fields) accociated with the last date with the last date of access (datAccess can be repeated, in this case i need the last one (by Id)). I.e. the desired result is:

+-----------+------------+--------+--------+--------+
| lClientId | datAccess  | lProp1 | lProp2 | lProp3 |
+-----------+------------+--------+--------+--------+
|         5 | 10/12/2020 |     3  |   10   |      9 |
|         6 | 10/29/2020 |     9  |   11   |     16 |
+-----------+------------+--------+--------+--------+

I tried to create several subqueries with SELECT which selects the maximum of each column with corresponding maximum date but the performance is falling down. Is there a way to select desiried values by single query? Because this is very expensive to go thru all the rows in table each time it search maximum value of date. The single pass is enough to get desiried values.

I use ADODB and VBA so maybe it's better to go thru all the rows and extract desiried fields manually? Using Recordset.MoveNext i can check all the fields by using single pass. Just i thought the engine is faster than if i access each field by Recordeset/Variant/etc.

The database is MS ACCESS.

Thanks in advance.

3
  • are the iprob number always rising= or could they fall also?
    – nbk
    Dec 12, 2020 at 21:55
  • The size of table about 100000 entries.
    – John
    Dec 12, 2020 at 21:58
  • lProp can be any.
    – John
    Dec 12, 2020 at 21:58

1 Answer 1

0

This is the SQL view of a msaccess query for your table

SELECT 
    t1.[lClientId]
    ,MAX(t1.datAccess) AS datAccess
    , MAX((SELECT TOP 1  t2.[lProp1] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp1] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp1] DESC))  AS lProp1
    , MAX((SELECT TOP 1  t2.[lProp2] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp2] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp2] DESC))  AS lProp2
    , MAX((SELECT TOP 1  t2.[lProp3] FROM Table1 AS t2 WHERE t2.[lClientId] = t1.[lClientId] AND t2.[lProp3] IS NOT NULL ORDER BY t2.datAccess  DESC, t2.[lProp3] DESC))  AS lProp3
FROM Table1 AS t1
GROUP BY t1.[lClientId];

Result

lClientId datAccess lProp1 lProp2 lProp3
5 10.12.2020 3 10 9
6 29.10.2020 9 11 16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.