Home:ALL Converter>MS-Access Select 1 row from GROUP BY query

MS-Access Select 1 row from GROUP BY query

Ask Time:2021-09-22T05:38:00         Author:JJJones_3860

Json Formatter

Always had a hard time wrapping my head around GROUP BY functionality, and this one is no exception.

I have a simple Join query as such

Select t1.g1, t1.g2, t2.id, t2.datetime, t3.name 
From ((table1 t1 Inner Join table2 t2 on t1.fld1=t2.fld1)
Inner Join table3 t3 on t1.fld2=t3.fld2)
Order By t2.datetime, t2.id

This returns my data as expected. Here are some sample rows that illustrate what I am trying to retrieve with Group By...

t1.g1 t2.g2 t2.id t2.datetime t3.name
726 4506 32 9/12/2021 nameA
726 4506 33 9/12/2021 nameB
726 4506 30 9/13/2021 nameC

Author:JJJones_3860,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/69275829/ms-access-select-1-row-from-group-by-query
Gordon Linoff :

\nI want to grab ONLY the first row in each Group of t1.g1, t1.g2.\n\nYou don't want aggregation. You want to filter the data. In this case, a correlated subquery does what you want:\nSelect t1.g1, t1.g2, t2.id, t2.datetime, t3.name \nFrom (table1 t1 Inner Join\n table2 t2\n on t1.fld1 = t2.fld1\n ) Inner Join\n table3 t3\n on t1.fld2 = t3.fld2\nwhere t2.id = (select top 1 tt2.id\n from (table1 tt1 Inner Join\n table2 tt2\n on tt1.fld1 = tt2.fld1\n ) Inner Join\n table3 tt3\n on tt1.fld2 = tt3.fld2\n where tt1.g1 = t1.g1 and tt1.g2 = t1.g2\n order by tt2.datetime, tt2.id\n );\n",