mysql – 如何选择最大日期的行

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE       
1    | 39    | 2014-08-01     | PRESENT       
2    | 42    | 2014-08-10     | ABSENT            
3    | 39    | 2014-08-12     | PRESENT           
4    | 43    | 2014-08-05     | PRESENT        
5    | 42    | 2014-07-15     | NULL           
6    | 39    | 2014-07-03     | ABSENT       
7    | 41    | 2014-08-01     | PRESENT    

我想选择最大PE_DATE_EXAM值,其中idnum = 39,ATTENDANCE = PReSENT

结果应该是：

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE
-------------------------------------------
3    | 39    | 2014-08-12     | PRESENT

SELECT * FROM tblPE t
WHERE t.idnum = 39 AND t.ATTENDANCE = 'PRESENT'
ORDER BY t.PE_DATE_EXAM DESC
LIMIT 1;

如果你希望它对所有idnum都是动态的,你可以使用NOT EXISTS()：

SELECT * FROM tblPE t
WHERE NOT EXISTS(SELECT 1 FROM tblPE s
                 WHERE t.idnum = s.idnum AND s.ATTENDANCE = 'PRESENT')
  AND t.attendance = 'PRESENT'