In a MySQL SELECT statement, how can a derived field use the value of another field in the SELECT list?

Question

In a MySQL SELECT statement, how can a derived field use the value of another field in the SELECT list?

For example, when you run the following query:

SELECT 'tim' AS first_name ,first_name || ' example' AS full_name; 

I expect the result to be:

 first_name, full_name tim , tim example 

Instead, I get the following error :

 Unknown column 'first_name' in 'field list'. 

Is there a way I can refer to another column?

thanks
Turgs

Answer 1

No, you have to repeat it or use a view.

 select *, concat(first_name, ' example') as full_name from ( select 'tim' as first_name ) as t 

Answer 2

You either complete the request as a subquery:

 SELECT first_name , first_name || ' example' AS full_name FROM ( SELECT 'tim' AS first_name , ... FROM ... ... ) AS tmp 

or duplicate code:

 SELECT 'tim' AS first_name , 'tim' || ' example' AS full_name 

Answer 3

From http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

Standard SQL prohibits references to column aliases in the WHERE clause. This restriction is imposed because when the WHERE clause is computed, the column value may not yet be defined.

You cannot reference a column whose value is not yet defined.