JPG JPG - 4 months ago 38
SQL Question

How do mysql order by when column's alias and column's name are the same?

Related this Receive range of dates. If starting dating doesn't exist fetch range from the closest past date, I have encountered a weird issue.

Here table

HistoryPrices
like this:

| Date | Price |
|------------|-------|
| 2015-12-25 | 44.5 |
| 2016-01-01 | 90.5 |
| 2016-01-03 | 50.5 |
| 2016-01-04 | 45.6 |
| 2016-01-10 | 40.99 |
| 2016-01-15 | 50.5 |
| 2016-01-22 | 50.99 |


When I ran this query:

select *
from HistoryPrices
order by `date` desc
limit 1;


I got the right row
| 2016-01-22 | 50.99 |
.

When I ran this:

select '2016-01-22', Price
from HistoryPrices
order by `date` desc
limit 1;


Also the right expected result
| 2016-01-22 | 50.99 |


And this query:

select '2016-01-22' as xxx, Price
from HistoryPrices
order by `date` desc
limit 1;


Also
| 2016-01-22 | 50.99 |


However, when I ran this:

select '2016-01-22' as `date`, Price
from HistoryPrices
order by `date` desc
limit 1;


I got this result
| 2016-01-22 | 44.5 |
. I have no idea why this happened. Maybe the column alias and the column name are the same
date
, then when
order by `date`
, mysql don't know how to do that? But I can not confirm this.

Can anyone do some explanation to me please?

Any help will be appreciated. Thanks in advance.

Answer

MySQL allows column aliases to be referenced in the order by. If you want the table name, then qualify the column:

select '2016-01-22' as `date`, hp.Price
from HistoryPrices hp
order by hp.`date` desc
limit 1;
Comments