mysql query with group by and order by

Question

I have a table like this:

.--------------------------------.
| Name  | XX | Date              |
.--------------------------------.
| N1    | 5  | 2009-05-01        |
| N1    | 10 | 2008-01-08        |
| N2    | 8  | 2009-02-02        |
.________________________________.

My result should be this:

.------------.
| Name  | XX |
.------------.
| N1    | 5  |
| N2    | 8  |
.____________.

I just want the rows, grouped by Name but just the newest ones. I dont want the row with XX=10 in this example because the date 2009-05-01 > 2008-01-08.

I don't know how to sort by Date and group it :(

Answer 1

SELECT Name, XX FROM myTable GROUP BY Name ORDER BY Date DESC

You may need to put quotes around your date field

`Date`

Answer 2

What do you want the grouping for?

select Name, XX
  from yourTable t1
 where Date = (select MAX(Date)
                 from yourTable t2
                where t1.Name = t2.Name)

If you need to sum XX for the last day, then:

select Name, SUM(XX)
  from yourTable t1
 where Date = (select MAX(Date)
                 from yourTable t2
                where t1.Name = t2.Name)
 group by Name

Answer 3

Sorry but these 2 are both wrong.

You should

SELECT Name, XX
 FROM t1
 JOIN (SELECT Name, max(date)
        FROM t1
        GROUP BY Name) subquery
   ON t1.Name = subquery.Name AND t1.Date = subquery.Date