mysql选择日期_MySQL选择最新日期的记录

I have two tables: COURSES and PERMISSIONS. I'm trying to do a SELECT that shows the latest permission value for all existing courses of a given student, in a given account.

Right now, I do this:

SELECT COURSES.NAME, PERMISSIONS.VALUE, PERMISSIONS.TS

FROM COURSES LEFT JOIN PERMISSIONS

ON PERMISSIONS.C_ID = COURSES.C_ID AND PERMISSIONS.S_ID = '12345'

WHERE COURSES.A_ID = 'ABCDE'

The result I get is

NAME | VALUE | TS

-----------------------------------------------

Mathematics | 1 | 2012-01-19 19:13:21

Mathematics | 0 | 2012-01-19 19:13:15

Mathematics | 0 | 2012-01-19 19:20:19

Mathematics | 0 | 2012-01-19 19:20:27

Mathematics | 0 | 2012-01-19 19:21:29

Biology | |

What I need is to show only the record with the latest timestamp (TS) so it shows this:

NAME | VALUE | TS

-----------------------------------------------

Mathematics | 0 | 2012-01-19 19:21:29

Biology | |

I tried GROUP BY on the course name in conjunction with an ORDER BY on the timestamp but it didn't pick up the correct record.

Can anyone please help?

解决方案SELECT COURSES.NAME, PL.VALUE, PL.TS

FROM COURSES

LEFT JOIN

( SELECT

PERMISSIONS.C_ID, PERMISSIONS.VALUE, PERMISSIONS.TS

FROM PERMISSIONS

JOIN

( SELECT P.C_ID, MAX(P.TS) AS LATEST

FROM PERMISSIONS P

WHERE P.S_ID = '12345'

GROUP BY P.C_ID ) PG

ON PERMISSIONS.TS = PG.LATEST ) PL

ON PL.C_ID = COURSES.C_ID

WHERE COURSES.A_ID = 'ABCDE'

Depending on the size of your data you might consider adding an index on (C_ID,TS) for PERMISSIONS table to speed up finding latest date:

ALTER TABLE `PERMISSIONS` ADD INDEX (`C_ID`,`TS`);