Zhang Mingli
www.hashdata.xyz
On Jan 6, 2024 at 23:38 +0800, Geoff Winkless <pgsqladmin@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.