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?
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).