Show HN: PRQL in PostgreSQL

Re:Show HN: PRQL in PostgreSQL by OJFord on Monday February 19, 2024 @12:32PM (#39432361)

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.

[ Parent | on HN ]

Re:Show HN: PRQL in PostgreSQL by da_chicken on Monday February 19, 2024 @01:48PM (#39433230)

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.

[ Parent | on HN ]

Re:Show HN: PRQL in PostgreSQL by riku_iki on Monday February 19, 2024 @03:36PM (#39434506)

your query will return just album_id and id, not other track fields

[ Parent | on HN ]