Hah, indeed, that doesn't work in PostgreSQL, neither does any other expression on a (in ORDER BY). So, I'd also like to conclude this, with my pinned tweet: https://twitter.com/lukaseder/status/1526262253284106253
(The following would work, but I forgot what the point of all this is): select a from ( select row (x, y) as a from (values (1, 2)) t (x, y) ) as t order by (a).f1; On Wed, Oct 11, 2023 at 8:39 AM Yafl Wabei <[email protected]> wrote: > Being late with replying, but I still want to conclude this. > > On Friday, 29 September 2023 at 08:38:30 UTC+2 Lukas Eder wrote: > > > In SQL, the column that you've nested ceases to exist outside of the UNION > operator because you've nested it. This is different without a UNION (and > without DISTINCT), because the column names that are not projected with > SELECT may still be accessed by ORDER BY in most "reasonable" dialects. > Though again, there might be exceptions, e.g.: > https://github.com/trinodb/trino/issues/18726 > > > I mentioned DISTINCT, because that creates a similar situation like UNION. > I've described in in this blog post: > https://blog.jooq.org/how-sql-distinct-and-order-by-are-related/ > > > Ah, so is this the reason why the version without UNION works? Because it > is accessing the table name and not the alias? Here my thinking was wrong. > > Good to know! In fact, separating the names of different things, I can > validate this – the following doesn't work: > > select > row (p.id, p.first_name, p.last_name, ...) as a, > book.title > from author as p > join book on p.id = book.author_id > where ... > order by a.first_name > > > Use (a).first_name to dereference nested row attributes in PostgreSQL > > > Thanks for the hint! Though, when I try this, I get an error > > «ERROR: column "a" does not exist» > > Did I miss something? Do I need a different syntax? > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/abe6ad0e-815d-47e3-9007-26fa6eb0cc06n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/abe6ad0e-815d-47e3-9007-26fa6eb0cc06n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6CxuZ2CuB2MOSKPoS0SpEM91ociB5NagoP5qCGuBi7YA%40mail.gmail.com.
