mysql 时间最大的_MySQL选择最大日期和最大时间

I have this kind of table:

CREATE TABLE Buckets (

id INT UNSIGNED AUTO_INCREMENT,

checkDate DATE NOT NULL,

checkTime TIME NOT NULL,

accountId CHAR(13),

costCenter VARCHAR(30),

percentage DECIMAL(5,2),

UNIQUE INDEX (checkDate, checkTime, accountId, costCenter),

PRIMARY KEY (id)

)ENGINE=InnoDB;

There I have currently this kind of data:

1 2014-03-24 08:11:27 387909559196 72350 86.92

2 2014-03-24 08:11:27 387909559196 analytics 12.71

3 2014-03-24 08:11:27 387909559196 json-files 0.36

4 2014-03-24 08:11:27 387909559196 cloud 0.01

5 2014-03-25 08:11:27 387909559196 72350 86.92

6 2014-03-25 08:11:27 387909559196 analytics 12.71

7 2014-03-25 08:11:27 387909559196 json-files 0.36

8 2014-03-25 08:11:27 387909559196 cloud 0.01

9 2014-03-25 08:38:55 387909559196 72350 86.92

10 2014-03-25 08:38:55 387909559196 analytics 12.71

11 2014-03-25 08:38:55 387909559196 json-files 0.36

12 2014-03-25 08:38:55 387909559196 cloud 0.01

I would like to make Select which selects max date and then max time. In this case results should include only rows 9-12.

So far I have tried this kind of query, but unfortunately I'm not getting the results what I want:

SELECT b.id, b.checkDate, b.checkTime, b.AccountId, b.costCenter, b.percentage, c.id, c.name FROM Buckets b

JOIN CustomerAccounts ca ON (b.accountId= ca.linkedAccountId)

JOIN Customer c ON (ca.customerId = c.id)

WHERE checkDate = (SELECT max(checkDate) FROM Buckets)

AND checkTime = (SELECT max(checkTime) FROM Buckets)

解决方案

Try like this

SELECT * FROM

(

SELECT b.id, b.checkDate, b.checkTime, b.AccountId, b.costCenter, b.percentage, c.id, c.name

FROM Buckets b JOIN CustomerAccounts ca ON (b.accountId= ca.linkedAccountId)

JOIN Customer c ON (ca.customerId = c.id)

) AS S JOIN

(

SELECT max(checkDate) AS MaxDate,max(checkTime) As MaxTime FROM Buckets

) AS T ON T.MaxDate = S.checkDate AND S.checkTime = T.MaxTime