Woo, it’s a complex order by, I try to understand your example.
And I think the order is right, what’s your expected order result?
```
ORDER BY
CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END NULLS FIRST,
CASE WHEN GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)))=0 THEN concat(test1.n, (SELECT x FROM test2 WHERE
seq=test1.seq)) ELSE NULL END NULLS FIRST;
```
You want to Order by a, b where a is: CASE WHEN GROUPING(test1.n)=0 THEN test1.n ELSE NULL END NULLS FIRST.
GROUPING(test1.n)=0 means that your are within grouping set test1.n and the value is test1.n, so results of another grouping
set b is NULL, and you specific NULL FIRST.
So your will first get the results of grouping set b while of course, column gp_n GROUPING(test1.n) is 1.
The result is very right.
gp_n | gp_conc | n | concat
------+---------+------+--------
1 | 0 | NULL | n5x1
1 | 0 | NULL | n4x2
1 | 0 | NULL | n3x3
1 | 0 | NULL | n2x4
1 | 0 | NULL | n1x5
0 | 1 | n1 | NULL
0 | 1 | n2 | NULL
0 | 1 | n3 | NULL
0 | 1 | n4 | NULL
0 | 1 | n5 | NULL
(10 rows)
NB: the Grouping bit is set to 1 when this column is not included.
https://www.postgresql.org/docs/current/functions-aggregate.html GROUPING ( group_by_expression(s) ) → integer
Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included
I guess you misunderstand it?
And your GROUPING target entry seems misleading, I modify it to:
SELECT GROUPING(test1.n, (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))))::bit(2),
test1.n, CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))
FROM test1
…skip
To show the grouping condition:
grouping | n | concat
----------+------+--------
10 | NULL | n5x1
10 | NULL | n4x2
10 | NULL | n3x3
10 | NULL | n2x4
10 | NULL | n1x5
01 | n1 | NULL
01 | n2 | NULL
01 | n3 | NULL
01 | n4 | NULL
01 | n5 | NULL
(10 rows)