On Fri, 5 Jan 2024 at 18:34, Zhang Mingli <zmlpostg...@gmail.com> wrote: > > 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?
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. > 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? I don't think I did. I pass GROUPING(something) and if the current set is being grouped by (something) then the return value will be 0. > 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) With respect, I've no idea why you think that's any clearer. Geoff
