3 ms·
In the real world, probably something like: select album_id ,first_value(id order by milliseconds desc) as longest_track_id from tracks
by OJFord 3y ago
In the real world, probably something like:
select
album_id
,first_value(id order by milliseconds desc) as longest_track_id
from tracks
group by album_id
I agree the PRQL's pretty nice here, but I think such a generalised example (chances you actually want to `select *`?) overstates the advantage.
- da_chicken 3y agoThis query would error: "Field `id` is neither in the GROUP BY nor in an aggregate function." Since `id` is a key in `tracks` and `album_id` is a foreign key and not unique in `tracks`, the RDBMS wouldn't be able to use any implicit GROUP BY determinism, either. You could do this: select distinct album_id ,first_value(id order by milliseconds desc) as longest_track_id from tracks But this is unlikely to perform as well as the row_number() method because it will cause the RDBMS to generate a record for every track and then waste time sorting the intermediate results to find the unique records in the output.
- riku_iki 3y agoyour query will return just album_id and id, not other track fields