On Mon, 8 Jan 2024 at 10:23, Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> Seems there was a reason why I thought that: per the documentation: > > "The arguments to the GROUPING function are not actually evaluated, > but they must exactly match expressions given in the GROUP BY clause > of the associated query level." > > https://www.postgresql.org/docs/16/functions-aggregate.html#FUNCTIONS-GROUPING-TABLE To throw a spanner in the works, it looks like it's not the test itself that's failing: it's putting the ORDERing in a CASE at all that fails. ... ORDER BY CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, CASE WHEN true THEN 2 ELSE 2 END; n | concat ----+-------- n1 | n2 | n3 | n4 | n5 | | n3x3 | n5x1 | n2x4 | n1x5 | n4x2 but without the CASE works fine: ... ORDER BY CASE WHEN GROUPING(test1.n) THEN 1 ELSE NULL END NULLS FIRST, 2; n | concat ----+-------- n4 | n2 | n3 | n5 | n1 | | n1x5 | n2x4 | n3x3 | n4x2 | n5x1 What's even more of a head-scratcher is why fixing this this then breaks the _first_ group's ORDERing. It _looks_ like removing the CASE altogether and ordering by the GROUPING value for all the grouping sets first: ORDER BY GROUPING(test1.n,CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))), 1, 2; actually works. I'm trying to figure out if that scales up or if it's just dumb luck that it works for my example. Geoff
