Use something like TOP with GROUP BY

aggregate, greatest-n-per-group, postgresql, sql

Solution

You can conveniently retrieve the passenger with the longest name per group with `DISTINCT ON`.

- Select first row in each GROUP BY group?

But I see no way to combine that (or any other simple way) with your original query in a single `SELECT`. I suggest to join two separate subqueries:

SELECT *
FROM  (  -- your original query
   SELECT orig
        , count(*) AS flight_cnt
        , count(distinct passenger) AS pass_cnt
        , percentile_cont(0.5) WITHIN GROUP (ORDER BY bags) AS bag_cnt_med
   FROM   table1
   GROUP  BY orig
   ) org_query
JOIN  (  -- my addition
   SELECT DISTINCT ON (orig) orig, passenger AS pass_max_len_name
   FROM   table1
   ORDER  BY orig, length(passenger) DESC NULLS LAST
   ) pas USING (orig);

`USING` in the join clause conveniently only outputs one instance of `orig`, so you can simply use `SELECT *` in the outer `SELECT`.

If `passenger` can be NULL, it is important to add `NULLS LAST`:

- PostgreSQL sort by datetime asc, null first?

From multiple passenger names with the same maximum length in the same group, you get an arbitrary pick - unless you add more expressions to `ORDER BY` as tiebreaker. Detailed explanation in the answer linked above.

Performance?

Typically, a single scan is superior, especially with sequential scans.

The above query uses two scans (maybe index / index-only scans). But the second scan is comparatively cheap unless the table is too huge to fit in cache (mostly). Lukas suggested an alternative query with only a single `SELECT` adding:

, (ARRAY_AGG (passenger ORDER BY LENGTH (passenger) DESC))[1]  -- I'd add NULLS LAST

The idea is smart, but last time I tested, `array_agg` with `ORDER BY` did not perform so well. (The overhead of per-group `ORDER BY` is substantial, and array handling is expensive, too.)

The same approach can be cheaper with a custom aggregate function `first()` like instructed in the Postgres Wiki here. Or, faster, yet, with a version written in C, available on PGXN. Eliminates the extra cost for array handling, but we still need per-group `ORDER BY`. May be faster for only few groups. You would then add:

 , first(passenger ORDER BY length(passenger) DESC NULLS LAST)

Gordon and Lukas also mention the window function `first_value()`. Window functions are applied after aggregate functions. To use it in the same `SELECT`, we would need to aggregate `passenger` somehow first - catch 22. Gordon solves this with a subquery - another candidate for good performance with standard Postgres.

`first()` does the same without subquery and should be simpler and a bit faster. But it still won't be faster than a separate `DISTINCT ON` for most cases with few rows per group. For lots of rows per group, a recursive CTE technique is typically faster. There are yet faster techniques if you have a separate table holding all relevant, unique `orig` values. Details:

- Optimize GROUP BY query to retrieve latest record per user

The best solution depends on various factors. The proof of the pudding is in the eating. To optimize performance you have to test with your setup. The above query should be among the fastest.

Problem

Original source

Related problems

• Optimize GROUP BY query to retrieve latest row per user
• Select first row in each GROUP BY group?
• Sort by column ASC, but NULL values first?