5 ms·
select id from tracks qualify row_number() over partition by (album_id order by milliseconds desc) =1 That should work Look I’m a diehard SQL just use it guy
by gigatexal 3y ago
select id
from tracks
qualify row_number() over partition by (album_id order by milliseconds desc) =1
That should work
Look I’m a diehard SQL just use it guy but open to improvements. But I’m loathe to use abstractions for things when the underlying thing is so expressive.
Autocomplete of fields in a good editor, schema help, etc go a long way to making SQL being written raw very nice.
- orlp 3y agoQUALIFY is not part of the SQL standard.
- gigatexal 3y agoThen a subselect with the window function and an outer where clause where the window function column = 1
- da_chicken 3y agoI'm pretty sure QUALIFY was added in SQL 2023. Maybe it was only discussed and didn't make it.