Postgres GROUP BY, then sort

Question

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 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   ║         ║
╚═════╩══════════╩════╩════╩

Answer 1

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:

• PostgreSQL - Referencing another aggregate column in a window function

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:

• GROUP BY + CASE statement
• PostgreSQL Where count condition

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:

• Sort by column ASC, but NULL values first?

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

Answer 2

You just want order by. Group by reduces (in general) the number of rows by aggregation.

You can accomplish this using window functions:

SELECT Foo, Bar, Bar2,
From FooBar
ORDER BY MAX(Bar) OVER (PARTITION BY Foo) DESC,
         Foo;

Answer 3

Shouldn't this do what you're asking for?

If not, it would help if you could give some sample data in the query and show how you'd want it as the output.

SELECT 
  Foo,
  MAX(some_calc) as Bar,
  MAX(some_other_calc) as Bar2,
From
 FooBar
##some inner joins for the calcs
GROUP BY FOO;