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].
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.
