SQL: Get Latest Record By Group

Question: How to get the latest record of each group?

Answer:

SELECT * FROM tbl_name AS ta,
   (SELECT grp_id,max(date) AS last_date FROM tbl_name GROUP BY grp_id) AS tb 
    WHERE ta.grp_id=tb.grp_id AND ta.date=tb.last_date ;

#-- 0.0016s

SELECT * FROM tbl_name AS ta WHERE NOT EXISTS 
    (SELECT * FROM tbl_name WHERE grp_id=ta.grp_id AND date>ta.date); 

#-- 0.0247s

SELECT * FROM tbl_name AS ta 
    WHERE ta.date=(SELECT max(date) FROM tbl_name WHERE grp_id=ta.grp_id); 

#-- 0.0415s

SELECT * FROM tbl_name AS ta 
    WHERE id IN (SELECT max(id) FROM tbl_name GROUP BY grp_id); 

#-- 2.0143s

分享到：