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 calcsGROUP BY FOOORDER BY Bar DESC, Bar2 DESC;I want to order by database with the order query, and then group together FOOs so that that first grouped block contains the FOO with the greatest Bar. The second grouped block of FOOs 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 ║ ║╚═════╩══════════╩════╩════╩Advertisement
Answer
SELECT foo, <some calc> AS bar, bar2FROM foobarORDER 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. See:
Assuming you want bigger foo first in case of ties with bar.