Latest datetime from unique mysql index

问题:

I have a table. It has a pk of id and an index of [service, check, datetime].

id  service  check   datetime  score
---|-------|-------|----------|-----
1  | 1     |  4    |4/03/2009 | 399
2  | 2     |  4    |4/03/2009 | 522
3  | 1     |  5    |4/03/2009 | 244
4  | 2     |  5    |4/03/2009 | 555
5  | 1     |  4    |4/04/2009 | 111
6  | 2     |  4    |4/04/2009 | 322
7  | 1     |  5    |4/05/2009 | 455
8  | 2     |  5    |4/05/2009 | 675

Given a service 2 I need to select the rows for each unique check where it has the max date. So my result would look like this table.

id  service  check   datetime  score
---|-------|-------|----------|-----
6  | 2     |  4    |4/04/2009 | 322
8  | 2     |  5    |4/05/2009 | 675

Is there a short query for this? The best I have is this, but it returns too many checks. I just need the unique checks at it's latest datetime.

SELECT * FROM table where service=?;

回答1:

First you need find out the biggest date for each check

 SELECT `check`, MAX(`datetime`)
 FROM YourTable
 WHERE `service` = 2
 GROUP BY `check`

Then join back to get the rest of the data.

 SELECT Y.*
 FROM YourTable Y
 JOIN ( SELECT `check`, MAX(`datetime`) as m_date
        FROM YourTable
        WHERE `service` = 2
        GROUP BY check) as `filter`
  ON Y.`service` = `filter`.service
 AND Y.`datetime` = `fiter`.m_date
 WHERE Y.`service` = 2