Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Select latest one from each category (group-wise max in a group query)

Select latest one from each category (group-wise max in a group query)

Author: rootsql Date: 7-11-2011, 22:17

Suppose we have this 'catalogs' table -

CREATE TABLE catalogs(
  id INT(11) NOT NULL,
  cat_id INT(11) DEFAULT NULL,
  name VARCHAR(50) DEFAULT NULL,
  `date` DATE DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO catalogs VALUES 
  (1, 1, 'suzy', '2011-09-15'),
  (2, 2, 'andy', '2011-10-01'),
  (3, 1, 'dony', '2010-12-25'),
  (4, 3, 'harry', '2010-01-05'),
  (5, 2, 'matty', '2011-06-01'),
  (6, 3, 'samy', '2010-11-02'),
  (7, 1, 'honey', '2011-10-03');

This query will select the latest one from each category -

SELECT t1.cat_id, t1.id FROM catalogs t1
  JOIN (
        SELECT cat_id, MAX(date) last_date FROM catalogs
          GROUP BY cat_id
        ) t2
    ON t1.cat_id = t2.cat_id AND t1.date = t2.last_date
ORDER BY t1.cat_id;
+--------+----+
| cat_id | id |
+--------+----+
|      1 |  7 |
|      2 |  2 |
|      3 |  6 |
+--------+----+
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.