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 need. 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 | +---------+---------+-----------------------+--------+------------+---------+ 

It’s hard for me to understand this. Thanks!

Answer 1

Online request request

http://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 

Answer 2

You can use the group by:

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

Answer 3

See SQL . Select only the rows with the maximum value in the 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; 

Answer 4

put this at the end of ORDER BY date DESC

So do this:

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

Using DISTINCT(Name) or GROUP BY Name in the above SQL statement, one item will appear only once.

Answer 5

You should use the GROUP BY clause as follows:

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

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