Hi, One problem is that this would not be backward compatible for some cases, if the select list uses the same identifier names as available in the selected tables.
Do you know which databases support it and which don't? Regards, Thomas On Friday, August 22, 2014, Lukas Eder <[email protected]> wrote: > Most SQL databases allow for referencing column aliases declared in the > <select list> in the <sort specification list> of the <order by clause>. > The following is specified to work by the SQL-1992 standard and also works > in H2: > > select x a from (select 1 x union select 2) t order by a > > > The SQL-1992 standard was very limited with respect to what's allowed in > ORDER BY clauses. E.g. no expressions were allowed at all, only column > references and column index references: > > 13.1 <declare cursor> > > > <order by clause> ::= > ORDER BY <sort specification list> > > <sort specification list> ::= > <sort specification> [ { <comma> <sort specification> }... ] > > <sort specification> ::= > <sort key> [ <collate clause > ] [ <ordering specification> ] > > > <sort key> ::= > <column name> > | <unsigned integer> > > > Luckily, we now have SQL:2011 and although the specs have become > completely unintelligible for most folks, I'd say that arbitrary > expressions should now be supported, and that those expressions should be > allowed to reference any column name from the <query expression>, including > "a" in the above example. In other words, this should be possible, but > isn't in H2: > > select x a from (select 1 x union select 2) t order by a + 1 > > > Or in a more real-world use-case, when implementing sort-indirection: > > select x a from (select 1 x union select 2) t order by case when a = 1 > then 1 else 0 end > > > I think that H2 should allow column aliases to be referenced from ORDER BY > clauses. > > Cheers, > Lukas > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
