CODEWITHSUNDEEP
mysqlwhere-clause
Bijan
Bijan

Reputation: 8602

SQL: Combining 'AS' and 'WHERE'

I have a column that contains version numbers in 4 groups (i.e. 13.8.0.262 or 9.0.0.0).

I have the following code that selects the version and splits it into the 4 groups and sorts it from oldest to newest

SELECT Version,
SUBSTRING_INDEX( `version` , '.', 1 )*1 as A,
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', 2 ),'.',-1)*1 as B, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', -2 ),'.',1)*1 as C,
SUBSTRING_INDEX( `version` , '.', -1 )*1 as D
FROM Test
WHERE A > 13
order by 
A,B,C,D

I am trying to filter the results, i.e. search where the first version (A) is > 14 but I get Unknown column 'A' in 'where clause'. Am I using the AS keyword incorrectly?

Here is a link to my SQL Fiddle

Upvotes: 1

Views: 31

Answers (2)

showdev
showdev

Reputation: 29168

You might find the HAVING clause useful:

SELECT Version,
SUBSTRING_INDEX( `version` , '.', 1 )*1 as A,
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', 2 ),'.',-1)*1 as B, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', -2 ),'.',1)*1 as C,
SUBSTRING_INDEX( `version` , '.', -1 )*1 as D
FROM Test
HAVING A > 13
order by 
A,B,C,D

Heres a working demonstration.

According to MySQL SELECT documentation, an alias cannot be accessed by a WHERE clause:

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.

but it can be accessed by a HAVING clause:

A HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions.

Also note that:

... the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. To accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in the SELECT list, MySQL 5.0.2 and up permit HAVING to refer to columns in the SELECT list, columns in the GROUP BY clause, columns in outer subqueries, and to aggregate functions.

For further reference, see Why does MySQL allow HAVING to use SELECT aliases?

Upvotes: 1

axiac
axiac

Reputation: 72226

An excerpt from the documentation of the SELECT MySQL statement:

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

And, a couple of paragraphs below:

It is not permissible to refer to a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed.

The answer to your question

As mentioned in the fragments quoted above, no, you cannot use the A alias in the WHERE clause. You have to use the aliased expression instead.

SELECT Version,
SUBSTRING_INDEX( `version` , '.', 1 )*1 as A,
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', 2 ),'.',-1)*1 as B, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `version` , '.', -2 ),'.',1)*1 as C,
SUBSTRING_INDEX( `version` , '.', -1 )*1 as D
FROM Test
WHERE SUBSTRING_INDEX( `version` , '.', 1 )*1 > 13
order by 
A,B,C,D

Upvotes: 2

Related Questions