Example demonstrates a way to find n-maximum records in the group.
CREATE TABLE comments( id INT(11) PRIMARY KEY AUTO_INCREMENT, post_id INT(11) ); INSERT INTO comments VALUES (1, 1), (2, 1), (3, 2), (4, 3), (5, 1), (6, 1), (7, 3), (8, 1);
Select 2 latest comments by post:
SELECT id, post_id FROM ( SELECT c1.*, COUNT(*) c_num FROM comments c1 LEFT JOIN comments c2 ON c2.post_id = c1.post_id AND c2.id <= c1.id GROUP BY c1.post_id, c1.id ) t WHERE c_num <= 2; +----+---------+ | id | post_id | +----+---------+ | 1 | 1 | | 2 | 1 | | 3 | 2 | | 4 | 3 | | 7 | 3 | +----+---------+
0 comments:
Post a Comment