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

How to sort two groups of foo with equally high bar? Smallest foo first or last? Can there be NULL values? Your version of Postgres? — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 19, 2016 at 1:16

Erwin Brandstetter · Accepted Answer · 2021-05-28 00:10:05Z

6

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:

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.

edited May 28, 2021 at 0:10

answered Apr 19, 2016 at 1:19

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DaynaJuliana Over a year ago

I still get column bar does not exist

Erwin Brandstetter Over a year ago

If bar refers to an expression, you have to spell out the expression in the window function and cannot refer to a column alias of the same expression in the SELECT list. You can refer to the output column name (alias) in ORDER BY and GROUP BY otherwise (but not in the WHERE or HAVING clause) . Detailed explanation here and here.

DaynaJuliana Over a year ago

Thanks, yes bar is an expression on two columns

Ankur Mahajan Over a year ago

@ErwinBrandstetter could you please give me some insights on stackoverflow.com/questions/59818667/…

Gordon Linoff · Accepted Answer · 2016-04-18 22:02:31Z

4

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;

answered Apr 18, 2016 at 22:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

DaynaJuliana Over a year ago

Bar is really some_calc as bar via an inner join. This only works with columns i think

xQbert Over a year ago

I think this is close. May need to sort by window desc, bar desc, foo... I say this because the window bar will always be max value for each foo, and thus you also need the sort by bar. and as to @daynaJuliana if there's inner calculations you could set it all up as a CTE or subselect.

Robins Tharakan · Accepted Answer · 2016-04-18 22:15:12Z

1

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;

answered Apr 18, 2016 at 22:15

Robins Tharakan

2,52122 silver badges19 bronze badges

2 Comments

Robins Tharakan Over a year ago

Could you give sample data and expected sorted output? I think more than one people are unable to comprehend as to what do you want to do here.

DaynaJuliana Over a year ago

Added a sample output

Collectives™ on Stack Overflow

Postgres GROUP BY, then sort

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related