MySQL query to get a column paired with maximum value of a second column

This is for MySQL, but there may be a more generally applicable SQL statement. I don't really need this to be portable, though.

I have a table like so:

| id | site | used | date |
| 1  | A    | 180  | 9/15 |
| 2  | A    | 110  | 9/14 |
| 3  | A    | 90   | 9/13 |
| 4  | B    | 200  | 9/15 |
| 5  | B    | 220  | 9/14 |
| 6  | B    | 270  | 9/13 |
| 7  | C    | 910  | 9/14 |
| 8  | C    | 930  | 9/13 |

Description: Data is captured every day, but Site C's last capture day was 9/14.

How do I get a result set that captures a single row for every site, with the maxmim value possible for date for that site?

开发者_运维百科

Results would be:

| id | site | used | date |
| 1  | A    | 180  | 9/15 |
| 4  | B    | 200  | 9/15 |
| 7  | C    | 910  | 9/14 |

---

select t.*
from (
    select site, max(date) as MaxDate
    from MyTable
    group by site
) tm
inner join MyTable t on tm.site = t.site and tm.MaxDate = t.Date

You will get duplicate rows if there is more than one site record with the same date, however.

---

SELECT * FROM (
   SELECT * FROM `test` ORDER BY date DESC
) AS t 
GROUP BY `site`