CODEWITHSUNDEEP
mysql
Cubi73
Cubi73

Reputation: 1951

Multiple aliases for one column

I know I can assign a temporary alias to a column. For example:

select foo, length(foo) as bar from my_table

And I know that I can use these aliases to get their values:

select foo, length(foo) as bar from my_table order by bar asc

But sometimes I want to make the results of my queries more readable, and when there is a space within a alias, I cannot use its value in the rest of the query:

select foo, length(foo) as "Foo's length" from my_table order by ? asc

Is there a way to define one alias for the query and one alias, that will be seen in the results?

Upvotes: 0

Views: 167

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

The reason it doesn't work is that MySQL is a bit schizophrenic about what a double quote means. Sometimes it means a string literal, sometimes it means a column alias.

When you write the query as you do;

select foo, length(foo) as "Foo's length" 
from my_table order by "Foo's length" asc

...it will actually create a column alias called Foo's length, but order by the string literal Foo's length. Since the string literal is the same for all rows, the ordering will be pseudo random.

What you need to do with MySQL is to quote your aliases consistently with backtick, and things will work well;

select foo, length(foo) as `Foo's length` 
from my_table order by `Foo's length` asc

An SQLfiddle showing the difference.

Upvotes: 1

ashishmaurya
ashishmaurya

Reputation: 1196

use this

select foo, length(foo) as "Foo's length" from my_table order by 2 asc

In order by 2 2 is the column index

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Why doesn't this work:

select foo, length(foo) as `Foo's length`
from my_table
order by `Foo's length` asc;

Or, use the old-style order by with a column reference:

select foo, length(foo) as `Foo's length`
from my_table
order by 2 asc;

Or, repeat the expression:

select foo, length(foo) as `Foo's length`
from my_table
order by length(foo) asc;

Upvotes: 1

Related Questions