Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Select N latest records in a group

Select N latest records in a group

Author: rootsql Date: 1-11-2011, 22:30

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 |
+----+---------+

Tags: Tips

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.