Search code examples
sqlsqlitegroup-by

Select first row in each group using GROUP BY

From a table in SQLite I am trying to get the first row of each group. Table geoview:

MaxOfStatecountpercent statepoolnumber STATECODE
123 1234 CA
123 1234 FL
234 1678 TX
234 1678 MN
234 0987 FL
234 0987 CA
234 9876 TX

I want to query the first of MaxOfStatecountpercent and STATECODE from each statepoolnumber. The equivalent Microsoft Access SQL query is:

select first(MaxOfStatecountpercent), statepoolnumber, first(STATECODE) from geoview group by statepoolnumber;

Output expected:

First(MaxOfStatecountpercent) statepoolnumber First(STATECODE)
123 1234 CA
234 1678 TX
234 0987 FL
234 9876 TX

LIMIT 1 did not work. How can I get a query equivalent in SQLite?

Solution

  • The following works for me with your sample table data:

    SELECT MaxOfStatecountpercent, statepoolnumber, STATECODE
FROM geoview
GROUP BY statepoolnumber
HAVING MIN(ROWID)
ORDER BY ROWID

    This assumes that, based on your desired input, you want the 'first' state of each statepoolnumber group to be the first record (rather than 'first' by alphabetical order).