A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685.
I have a simple table layout:
class person: belongs to a class
I want to select all classes, and for each class, I want the first two person identifiers of the belonging persons sorted by descending name.
I solved this with the following query:
select c.identifier, array_agg(p.identifier order by p.name desc) as persons from class as c left join lateral ( select p.identifier, p.name from person as p where p.class_identifier = c.identifier order by p.name desc limit 2 ) as p on true group by c.identifier order by c.identifier
Note: I could have used a correlation subquery in the
SELECT clause, but I am trying to avoid that as part of a learning process.
As you can see, I am applying
order by p.name desc in two places:
- in the subquery
- in the aggregate function
Is there a way to avoid that? My train of tought:
First, obviously I cannot remove the
order byin the subquery, as that would give a query which does not meet my requirement as stated above.
Second, I think that the
order byin the aggregate function cannot be left out, as row order of the subquery is not necessarily preserved in the aggregate function?
Should I rewrite the query?
(identifier)the primary key of