[SOLVED] Select first row in each group using group by in sqlite

Select first row in each group using group by in sqlite

I have a table in sqlite. I am trying to get the first row of each group. Although there were earlier questions regarding the same. I couldn't find solution in sqlite.

My table looks like this:

select * from 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 would like to query the first of MaxOfStatecountpercent and first of STATECODE from each statepoolnumber. The equivalent access sql query is :

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

And the output expected is :

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

I tried with "limit 1 " but 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).