How to get the most recent data from a table?

Here is the table that I have

|    Date | Value |
|---------|-------|
| 2020-01 |   598 |
| 2020-01 |   659 |
| 2020-01 |  1005 |
| 2020-03 |   478 |
| 2020-06 |   500 |
| 2020-06 |   898 |
| 2020-06 |   258 |
| 2020-06 |   654 |
| 2020-06 |   459 |
| 2020-03 |   656 |
| 2020-02 |   897 |
| 2020-02 |  1547 |
| 2020-05 |  2028 |

The question that I have for my query is “Using the most recent month of data available, what is the range of estimated home values across the nation?“

Since my table is small, I know the recent date is 2020-06.

So I wrote this code:

SELECT date, value
FROM home
WHERE date = '2020-06';

And the result that I get is:

|    Date | Value |
|---------|-------|
| 2020-06 |   500 |
| 2020-06 |   898 |
| 2020-06 |   258 |
| 2020-06 |   654 |
| 2020-06 |   459 |

Another method of code that I wrote is:

SELECT date, value
FROM home
Order by date DESC
LIMIT 5;

And I get the same result as above. But none of this two code is feasible if I have a big amount of data. So my question is, how can I get the most recent data without manually typing out the specific?

The sql fiddle is here

Answer

You can use a subquery:

SELECT date, value
FROM home
WHERE date = (SELECT MAX(date) FROM home);

Advertisement

Answer