How to combine ORDER BY and LIMIT with an aggregate function?

Question

Here is a fiddle for my question.

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 thought:

• First, obviously I cannot remove the order by in the subquery, as that would give a query which does not meet my requirement as stated above.

• Second, I think that the order by in 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?

Answer 1

I am applying order by p.name desc in two places ... Is there a way to avoid that?

Yes. Aggregate with an ARRAY constructor in the lateral subquery directly:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT ARRAY (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) AS persons
   ) p
ORDER  BY c.identifier;

You also don't need GROUP BY in the outer SELECT this way. Shorter, cleaner, faster.

I replaced the LEFT JOIN with a plain CROSS JOIN since the ARRAY constructor always returns exactly 1 row. (Like you pointed out in a comment.)

db<>fiddle here.

Related:

• Preserve order of array elements after join

Order of rows in subqueries

To address your comment:

I learned that order of rows in a subquery is never guaranteed to be preserved in the outer query.

Well, no. While the SQL standard does not offer any guarantees, there are limited guarantees in Postgres. The manual:

This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

If all you do in the next level is to aggregate rows, the order is positively guaranteed. Any yes, what we feed to the ARRAY constructor is a subquery, too. That's not the point. It would work with array_agg() as well:

SELECT c.identifier, p.persons
FROM   class c
CROSS  JOIN LATERAL (
   SELECT array_agg(identifier) AS persons
   FROM  (
      SELECT identifier
      FROM   person
      WHERE  class_identifier = c.identifier
      ORDER  BY name DESC
      LIMIT  2
      ) sub
   ) p
ORDER  BY c.identifier;

But I expect the ARRAY constructor to be faster for the case. See:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?
• convert right side of join of many to many into array

Answer 2

Here's an alternative, but it is not any better than what you already have:

with enumeration (class_identifier, identifier, name, n) as (
    select  p.class_identifier, p.identifier, p.name
         , row_number() over (partition by p.class_identifier 
                              order by p.name desc)
    from     person as p
)
select c.identifier, array_agg(e.identifier order by e.n) as persons
from class as c
left join  enumeration e
    on c.identifier = e.class_identifier
where e.n <= 2
group by   c.identifier
order by   c.identifier;