CODEWITHSUNDEEP
mysqlsql
Feirell
Feirell

Reputation: 789

MySQL 'AS' modifies selection

Ok I am lost, I have no idea why those both querys have different output.

The table looks something like this:
+------------+--------+--------+
|    date    | kills  | deaths |
+------------+--------+--------+
| 2016-05-03 | 123456 | 123456 |
+------------+--------+--------+

SELECT SUBDATE(CURRENT_DATE(),30), `kills`, `deaths` 
    FROM `bf4c_1558544842`
    WHERE `date` <= SUBDATE(CURRENT_DATE(),30) 
    ORDER BY `date` DESC 
    LIMIT 1

SELECT SUBDATE(CURRENT_DATE(),30) AS "date", `kills`, `deaths` 
    FROM `bf4c_1558544842`
    WHERE `date` <= SUBDATE(CURRENT_DATE(),30) 
    ORDER BY `date` DESC 
    LIMIT 1

The only difference is the AS "date", but why does that change the selection ? The first gets me the intended first after the given border and the second gives me the last in the table.

Could pleas someone explain me why this happens ?

Thanks in advance, Feirell.

Upvotes: 0

Views: 47

Answers (1)

axiac
axiac

Reputation: 72226

On the second query the expression SUBDATE(CURRENT_DATE(),30) is aliased as date. Later on, the selected rows are ORDER BY date and after the sort only the first row is returned.

The documentation of the SELECT statement explains:

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.

...

MySQL resolves unqualified column or alias references in ORDER BY clauses by searching in the select_expr values, then in the columns of the tables in the FROM clause.

This basically means that aliases have higher priority than column names.

There is a column named date in the table. In the first query, ORDER BY date uses it for sorting and you get the results you expect.

On the second query, the date alias is used by the SORT BY date clause. But since it aliases the constant expression SUBDATE(CURRENT_DATE(),30) all the selected rows have the same value for the date expression. They are already sorted, no matter what their order is. Any result is possible in this case.

Edit: A solution would be to add the table name in front of the date in the order by clause like this:

SELECT SUBDATE(CURRENT_DATE(),30) AS "date", `kills`, `deaths` 
    FROM `bf4c_1558544842`
    WHERE `date` <= SUBDATE(CURRENT_DATE(),30) 
    ORDER BY `bf4c_1558544842`.`date` DESC 
    LIMIT 1

This way the interpreter knows that the column is meant not the new alias.

Upvotes: 2

Related Questions