On Sat, Jan 6, 2024 at 8:38 AM Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> 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. > > Something does seem off here with the interaction between grouping sets and order by. I'm inclined to believe that using grouping in the order by simply is an unsupported concept we fail to prohibit. The discussion around union all equivalency and grouping happening well before order by lead me to this conclusion. You can get the desired result with a much less convoluted order by clause - so long as you understand where your nulls are coming from - with: https://dbfiddle.uk/Uk22nPIZ ORDER BY n nulls first , x nulls first Where x is the assigned alias for the concatenation expression column. David J.
