DaynaJuliana DaynaJuliana - 7 months ago 21
SQL Question

Postgres GROUP BY, then sort

I have a database query like:

SELECT
Foo,
Foo2,
some_calc as Bar,
some_other_calc as Bar2,
From
FooBar
-- some inner joins for the calcs
GROUP BY FOO
ORDER BY Bar DESC, Bar2 DESC;


I want to order by database with the order query, and then group together
FOO
s so that that first grouped block contains the
FOO
with the greatest Bar. The second grouped block of
FOO
s contains the seconds highest Bar, etc.

But this doesn't work as Postgres doesn't allow random grouping:

column "Bar" must appear in the GROUP BY clause or be used in an aggregate function
.

How can I fix this?

Sample data and output:

╔═════╦══════════╦════╦════╦
║ FO ║ Bar ║ Bar 2 ║
╠═════╬══════════╬═════════╬
║ 6 ║ 10 ║ ║
║ 4 ║ 110 ║ ║
║ 3 ║ 120 ║ ║
║ 8 ║ 140 ║ ║
║ 3 ║ 180 ║ ║
║ 3 ║ 190 ║ ║
╚═════╩══════════╩════╩════╩


Output:

╔═════╦══════════╦════╦════╦
║ FO ║ Bar ║ Bar 2 ║
╠═════╬══════════╬═════════╬
║ 3 ║ 190 ║ ║
║ 3 ║ 180 ║ ║
║ 3 ║ 120 ║ ║
║ 8 ║ 140 ║ ║
║ 4 ║ 110 ║ ║
║ 6 ║ 10 ║ ║
╚═════╩══════════╩════╩════╩

Answer
SELECT foo, <some calc> AS bar, bar2
FROM   foobar
ORDER  BY max(<some calc>) OVER (PARTITION BY foo) DESC NULLS LAST  -- can't refer to bar
        , bar DESC NULLS LAST  -- but you can here
        , foo DESC NULLS LAST;
  • bar does not have to be a column, can be any valid expression, even an aggregate function (in combination with GROUP BY) - just not another window function, which can't be nested. Example:

  • You cannot, however, refer to a column alias (output column name) on the same query level within a window function. You have to spell out the expression again, or move the calculation to a subquery or CTE.
    You can refer to output column names in ORDER BY and GROUP BY otherwise (but not in the WHERE or HAVING clause). Explanation:

  • Since it has not been defined we must expect NULL values. Typically you want NULL values last, so add NULLS LAST in descending order.

  • Assuming you want bigger foo first in case of ties with bar.