Hi,
Zhang Mingli www.hashdata.xyz On Jan 6, 2024 at 23:38 +0800, Geoff Winkless <pgsqlad...@geoff.dj>, wrote: > > I was hoping to see > > gp_n | gp_conc | n | concat > ------+---------+------+-------- > 1 | 0 | NULL | n1x5 > 1 | 0 | NULL | n2x4 > 1 | 0 | NULL | n3x3 > 1 | 0 | NULL | n4x2 > 1 | 0 | NULL | n5x1 > 0 | 1 | n1 | NULL > 0 | 1 | n2 | NULL > 0 | 1 | n3 | NULL > 0 | 1 | n4 | NULL > 0 | 1 | n5 | NULL > > because when gp_conc is 0, it should be ordering by the concat() value. Hi, I misunderstand and thought you want to see the rows of gp_n = 0 first. So you’re not satisfied with the second key of Order By. I simply the SQL to show that the difference exists: SELECT GROUPING(test1.n) AS gp_n, GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) AS gp_conc, test1.n, CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) FROM test1 GROUP BY GROUPING SETS( (test1.n), (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) ) HAVING n is NULL ORDER BY concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) NULLS FIRST; gp_n | gp_conc | n | concat ------+---------+------+-------- 1 | 0 | NULL | n1x5 1 | 0 | NULL | n2x4 1 | 0 | NULL | n3x3 1 | 0 | NULL | n4x2 1 | 0 | NULL | n5x1 (5 rows) This is what you want, right? And if there is a CASE WHEN, the order changed: SELECT GROUPING(test1.n) AS gp_n, GROUPING(concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) AS gp_conc, test1.n, CONCAT(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) FROM test1 GROUP BY GROUPING SETS( (test1.n), (concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq))) ) HAVING n is NULL ORDER BY CASE WHEN true THEN concat(test1.n, (SELECT x FROM test2 WHERE seq=test1.seq)) END NULLS FIRST; 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 (5 rows) I haven’t dinged into this and it seems sth related with CASE WHEN. A case when true will change the order.
