I have got some tables with overlapping column names and I want to combine those columns into one column with the same name as alias, e.g.
select a.name || " " || b.name as name from a join b
This works fine. However, if I want to order them, e.g.
order by lower(name) asc
I get the error ambiguous column name: name, because sqlite doesn't know whether to use a's, b's or the selection's name column. My question is whether it is possible to specifically chose the selection's name column (without giving it a different alias)? Maybe there is some nice <keyword> such that <keyword>.name results in the selection's name column. Or is there really no better solution than
with tmp as (select tmp_a.name || " " || tmp_b.name as name from tmp_a join tmp_b)
select name from tmp order by lower(name) asc
I don't think that the use of the CTE or a subquery is not a good solution.
If you don't like them, the only way to do it is to repeat the expression in the ORDER BY clause:
select a.name || ' ' || b.name as name
from a join b
on ......
order by lower(a.name || ' ' || b.name)