← Back to context

Comment by OJFord

2 years 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.

2 comments

OJFord

Reply
da_chicken  2 years ago

This 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.