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 '20 at 21:55
  • The size of table about 100000 entries. – John Dec 12 '20 at 21:58
  • lProp can be any. – John Dec 12 '20 at 21:58
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, privacy policy and cookie policy

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