How can I get the most recent date using MySQL

Question

How can I get the most recent date using MySQL? I tried max but I did not get the result that I want. My table looks like this:

+---------+---------+-----------------------+--------+------------+---------+
| Name    | idStore | Name                  | idItem | date       | price   |
+---------+---------+-----------------------+--------+------------+---------+
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-22 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-28 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-28 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-11-22 | 0.98000 |
| walmart |       1 | Honeycrisp Apples     |      2 | 2011-10-22 | 1.98000 |
| walmart |       1 | Sonya Apples          |      3 | 2011-10-22 | 2.88000 |
| walmart |       1 | Gold Delicious Apples |      4 | 2011-10-22 | 0.98000 |
| walmart |       1 | Sweet Tango Apples    |      5 | 2011-10-22 | 2.48000 |
| walmart |       1 | Granny Smith Apples   |      6 | 2011-10-22 | 1.28000 |
| walmart |       1 | Fugi Apples           |      7 | 2011-10-22 | 1.38000 |
+---------+---------+-----------------------+--------+------------+---------+

I want to get this table:

+---------+---------+-----------------------+--------+------------+---------+
| Name    | idStore | Name                  | idItem | date       | price   |
+---------+---------+-----------------------+--------+------------+---------+
| walmart |       1 | Red Delicious Apples  |      1 | 2011-11-22 | 0.98000 |
| walmart |       1 | Honeycrisp Apples     |      2 | 2011-10-22 | 1.98000 |
| walmart |       1 | Sonya Apples          |      3 | 2011-10-22 | 2.88000 |
| walmart |       1 | Gold Delicious Apples |      4 | 2011-10-22 | 0.98000 |
| walmart |       1 | Sweet Tango Apples    |      5 | 2011-10-22 | 2.48000 |
| walmart |       1 | Granny Smith Apples   |      6 | 2011-10-22 | 1.28000 |
| walmart |       1 | Fugi Apples           |      7 | 2011-10-22 | 1.38000 |
+---------+---------+-----------------------+--------+------------+---------+

I am having hard time figuring this out. Thanks!

You're not the only one that has a hard time figuring this out. I suggest you elaborate by giving some code you've tried to get the result, so we can see why it doesn't work. — kasimir, Nov 23 '11 at 08:05

score 3 · Answer 1 · answered Nov 23 '11 at 08:06

3

You can use group by:

select NameStore, idStore, Name, idItem, max(date) date, price
from table
group by NameStore, idStore, Name, idItem, price

answered Nov 23 '11 at 08:06

rMX

1,070
16
23

score 2 · Accepted Answer · edited Jan 18 '21 at 12:30

2

Online Example Query

https://data.stackexchange.com/stackoverflow/q/118881/how-can-i-get-the-most-resent-date-using-mysql

SELECT NameStore, idStore, Name, idItem, max(date) AS date, price
FROM tablename
GROUP by NameStore, idStore, Name, idItem, price
ORDER BY date DESC, idItem ASC

edited Jan 18 '21 at 12:30

Community

1
1

answered Nov 23 '11 at 08:41

Utku Yıldırım

2,277
16
20

Note : this won't work if he wants really only the most recent date, and the price that applied on that date, regardless of what other prices might have applied on elder dates. – Erwin Smout Nov 23 '11 at 15:48

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

See SQL Select only rows with Max Value on a Column

SELECT yt1.*
FROM yourtable yt1
LEFT OUTER JOIN yourtable yt2 ON (yt1.idItem = yt2.idItem AND yt1.date < yt2.date)
WHERE yt2.idItem IS NULL;

edited May 23 '17 at 12:08

Community

1
1

answered Feb 28 '14 at 21:53

jfountain

3,715
2
24
22

Tusk · Answer 4 · 2011-11-23T08:09:12.947

-1

put this at the end ORDER BY date DESC

So make it look like this:

SELECT * FROM `tablename` ORDER BY `date` DESC

Using DISTINCT(Name) or GROUP BY Name in the SQL sentence above, would make one item pop up only once.

edited Nov 23 '11 at 08:09

answered Nov 23 '11 at 08:04

Tusk

703
11
21

1

But he also want to only see one line per idItem. Hint: GROUP BY? – Konerak Nov 23 '11 at 08:05
And then see [this link](http://stackoverflow.com/questions/341737/mysql-changing-the-query-to-be-distinct-on-just-1-column) for distinct values – abhinav Nov 23 '11 at 08:08
-1 the order by is not what he needs; this will still keep the duplicate dates in there. – Eljakim Nov 23 '11 at 08:14
1

"How can I get the most recent date using MySQL?" is the Question, sorry for answering it. – Tusk Nov 23 '11 at 08:22

PiTheNumber · Answer 5 · 2011-11-23T08:25:07.580

-1

You have to use the GROUP BY clause like this:

SELECT Name, idStore, Name, idItem, date, price 
FROM mytable 
GROUP BY idItem 
ORDER BY date DESC, idItem DESC;

Change the order direction using DESC or ASC. You can learn more about this reading the documentation.

edited Nov 23 '11 at 08:25

answered Nov 23 '11 at 08:08

PiTheNumber

22,828
17
107
180

How can I get the most recent date using MySQL

5 Answers5