MYSQL group and order query

Question

I have a table with name and order date. I am trying to come up with a query to show the last time each name ordered in reverse order. So each names newest order but sorted by the name that ordered earliest.

So with this data

Tom,10/01/2013  
Bob,09/24/2013  
Tom,11/03/2013  
Tom,10/24/2013  
Bill,11/01/2013  
Bob,10/22/2013  

I want this

Bob,10/22/2013  
Bill,11/01/2013  
Tom,11/03/2013  

I have tried SELECT * from orders group by name order by -odate that got either random or older than the newest dates I then tried SELECT * from orders order by -odate group by name thinking it would sort first but it returned nothing

Thanks Ed

Answer 1

Try using MAX function for your date column

SELECT name, MAX(odate) from orders group by name order by odate

Answer 2

The key thing here is to GROUP BY the name, but to select the maximum value that you need.

When grouping you can perform range functions on other fields.

Using the ORDER BY DESC puts the most recent first.

SELECT name, MAX(odate) AS odate FROM orders GROUP BY name ORDER BY odate DESC

see SQL Fiddle

Answer 3

SELECT name, MAX(odate) odate FROM orders GROUP BY name ORDER BY odate