Ms-access – How to select maximum row value using optimal way

maxms access

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.

Best Answer

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
Related Question