select id, a, min(mtime) over(partition by id order by mtime) m from tab
qualify row_number() over(partition by id order by mtime) = 1
While using analytics would indeed be the best approach overall, these are
sadly not supported in SQLite in any ways or forms.
(For the record, if using analytics, the only thing one really need to do is to
mark the lead row for selection. No point to over complicate things as above).
what do you mean by "to mark the lead row for selection" ? is there a
database that has something simpler to use than qualify + row_number() ?
(yes i know, min can be replaced by first_value() or order by in most
aggregates can/should be removed, but after those changes it's still the
same query)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
