0

mysql table: stats

columns: 
         date           | stats
05-05-2015 22:25:00     | 78
05-05-2015 09:25:00     | 21
05-05-2015 05:25:00     | 25
05-04-2015 09:25:00     | 29
05-04-2015 05:25:00     | 15

sql query:

SELECT MAX(date) as date, stats FROM stats GROUP BY date(date) ORDER BY date DESC

when I do this, I does select one row per date (grouped by date, regardless of the time), and selects the largest date with MAX, but it does not select the corresponding column. for example, it returns 05-05-2015 22:25:00 as the date, and 25 as the stats. It should be selecting 78 as the stats. I've done my research and seems like solutions to this are out there, but I am not familiar with JOIN or other less-common mysql functions to achieve this, and it's hard for me to understand other examples/solutions so I decided to post my own specific scenario.

Narendrasingh Sisodia
  • 21,247
  • 6
  • 47
  • 54
Umar
  • 1
  • 1
  • 1
    possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – rghome May 20 '15 at 07:22

1 Answers1

1

This question is asked every single day in SO. Sometimes, it's correctly answered too. Anyway, purists won't like it but here's one option:

Select x.* from stats x join (SELECT MAX(date) max_date FROM stats GROUP BY date(date)) y on y.max_date = x.date;

Obviously, for this to work dates need to be stored using a datetime data type.

Strawberry
  • 33,750
  • 13
  • 40
  • 57