2

I have a table with the following columns.

  • ID (auto-inc)
  • When (datetime)
  • id1 (number)
  • id2 (number)

The combination of id1 and id2 can be unique or duplicated many times.

I need a query that returns the earliest record (by When) for each unique combination of id1+id2.

Example data:

ID When id1 id2
1 1-Jan-2020 4 5
2 1-Jan-2019 4 5
3 1-Jan-2021 4 5
4 1-Jan-2020 4 4
5 1-Jan-2019 4 4
6 1-Jan-2021 4 6

I need this to return rows 2, 5 and 6

I cannot figure out how to do this with an SQL query.

I have tried Group By on the concatenation of id1 & id2, and I have tried "Distinct id1, id2", but neither return the entire row of the record with the earliest When value.

If the result set can just return the ID that is fine also, I just need to know the rows that match these two requirements.

JJJones_3860
  • 1,382
  • 2
  • 15
  • 35
  • 1
    Does this answer your question? [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) – June7 Nov 21 '22 at 20:47
  • 1
    Or build aggregate query that returns Max ID or When for each id1/id2 combination and join that query to table. This will result in a non-editable dataset. – June7 Nov 21 '22 at 20:52
  • Thx June7. On your first comment, I can't see how that does help - or I don't understand how to make it work. On the 2nd comment, I simply don't understand your response to the level I need to in order to use it. – JJJones_3860 Nov 21 '22 at 21:03
  • I spent the last 20 minutes trying to get the reference post to work. – JJJones_3860 Nov 21 '22 at 21:03
  • 1
    Sorry, I should have said Min ID or When. – June7 Nov 21 '22 at 21:29

1 Answers1

4

Okay, I had a few minutes to kill:

SELECT Data.* FROM Data WHERE ID IN (
         SELECT TOP 1 ID FROM Data AS D 
         WHERE D.id1=Data.id1 AND D.id2=Data.id2 ORDER BY When);

or

SELECT Data.* FROM Data INNER JOIN (
     SELECT id1, id2, Min(When) AS MW FROM Data 
     GROUP BY id1, id2) AS D
ON Data.When = D.MW AND Data.id1=D.id1 AND Data.id2=D.id2;
ID When id1 id2
2 1/1/2019 4 5
5 1/1/2019 4 4
6 1/1/2021 4 6
June7
  • 19,874
  • 8
  • 24
  • 34
  • I just tried your first sql above on my table with 31,318 records, the query is still running after 4 minutes on a very robust computer (32GB Ram, Superfast SSD, and Ryzen 5G). I do need this to work in under 5s with 31k recs. – JJJones_3860 Nov 21 '22 at 21:37
  • 1
    If the second query isn't fast enough, then may need a VBA solution or migrate to SQLServer or MySQL or something else. – June7 Nov 21 '22 at 21:40
  • Thx for your help June7. I can simply process that table using VBA and get what I am after in under 5s. I probably should have mentioned that. So, I was just looking for an SQL query that would do the same thing, return in under 5s and avoid the extra code that I am already using. – JJJones_3860 Nov 21 '22 at 21:42
  • 1
    Add indices to id1 and id2 – tinazmu Nov 21 '22 at 22:15