Hi,
Zhang Mingli www.hashdata.xyz On Jan 6, 2024 at 01:38 +0800, Geoff Winkless <pgsqlad...@geoff.dj>, wrote: > > Am I missing some reason why the first set isn't sorted as I'd hoped? 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)
