SQLite Forum

Select first row in a group
Login

Select first row in a group

(1) By cgenie on 2022-01-25 20:08:48 [link] [source]

Hello,

PostgreSQL allows for SELECT DISTINCT ON query to select the first row in a group:

https://learnsql.com/blog/postgresql-select-distinct-on/

Is something like this possible in SQLite?

Best

(2.1) By Larry Brasfield (larrybr) on 2022-01-25 20:31:55 edited from 2.0 in reply to 1 [link] [source]

(Edited to correct for oversight.)

Yes, it is possible to do something much like that. Please read the latter part of the article you linked where it says how to do that without the PG-special ON syntax, and let us know if you cannot do it the same way with SQLite.

(4) By cgenie on 2022-01-26 06:32:04 in reply to 2.1 [link] [source]

I must be blind but I don't see any part of that linked article which doesn't mention the ON syntax?

(3.1) By Keith Medcalf (kmedcalf) on 2022-01-25 20:36:15 edited from 3.0 in reply to 1 [link] [source]

Yes.

  select location,
         min(time) as mintime,
         report
    from weather_reports
group by location
;

Would be the "standard" way of phrasing the query. Note that many other RDBMS have removed the capability to retrieve bare columns as part of a group-by query, though SQLite3 has not -- it is this removal of a "standard feature" from the 1960's that has resulted in the addition of other vendor-proprietary methods of achieving the same result.

See https://sqlite.org/lang_select.html and in particular section 2.5 thereof.

(5) By cgenie on 2022-01-26 06:32:55 in reply to 3.1 [source]

Ah ok great, I'll try that. Yes, the GROUP BY limitation to only the grouped columns was blocking me from thinking about this solution :)