[Resolved] Using Column Aliases in MySQL to Narrow Result Sets

[cbj4074]

Hey all,

I am trying to do a calculation in a MySQL query, but am running into trouble when I attempt to use an aliased column name (the values for which are calculated on-the-fly) in the WHERE clause:

'SELECT left_bound, right_bound, (left_bound - right_bound) AS difference FROM numbers WHERE difference < 10 ORDER BY difference ASC LIMIT 5'

yields the error:

Unknown column 'difference' in 'where clause'

I see where the error is coming from, but how would one go about referencing a "virtual" column whose values are calculated on-the-fly?

[cbj4074]

From the MySQL manual (http://dev.mysql.com/doc/mysql/en/select.html):

It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, “Problems with Column Aliases”.

Evidently, the solution is to use the HAVING clause instead of the WHERE clause. Always nice when one answers his own question.

Hope this helps someone else.