Edit and get data for a temporary query

user_14211693 0
Viewed 29

I have car specs table. I want to edit some of the data that meet the condition "condition: IF (SUBSTRING(car_model,9,1)=3,true,false ) ". I can create a new edited temporary column, but I can't use ORDER BY on it, it didn't recognize the new column.

How can I do that in the right way?

SELECT *, IF (SUBSTRING(car_model,9,1)=3, ROUND(car_performance*0.9),ROUND(car_performance*1)) AS car_newperformance FROM car_list WHERE car_newperformance > 3500 ORDER BY car_newperformance ASC

#1054 - Unknown column 'car_newperformance' in 'where clause'
mysql sql
1 Answers

MySQL extends the use of HAVING to support filtering on column aliases:

SELECT cl.*,
       IF(SUBSTRING(car_model, 9, 1) = 3, ROUND(car_performance * 0.9), ROUND(car_performance * 1)) AS car_newperformance
FROM car_list cl
HAVING car_performance > 3500
ORDER BY car_newperformance ASC;

This is MySQL-specific syntax. In other databases, you would use a subquery, CTE, or lateral join. SQL as a general rule does not recognize the column alias in the SELECT, FROM, or WHERE clauses.

user_11440350
Related