MySQL | How to select only one record of each category?

greatest-n-per-group, mysql, sql

Solution

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT Category, MAX(date) max_date
            FROM    tableName
            GROUP BY Category
        ) b ON a.category = b.category AND
                a.date = b.max_date

for better performance, add a compund `INDEX` on column `Category, date`

OR

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT Category, MAX(ID) max_ID
            FROM    tableName
            GROUP BY Category
        ) b ON a.category = b.category AND
                a.ID = b.max_ID

- SQLFiddle Demo (both queries)

Problem

I have a table that contains records with data, and each record belongs to a category. Lets say I have the table that following: ``` ID | Category | Title | Date -------------------------------------- 1 | Cat 1 | Ttl 1 | 2013-02-18 2 | Cat 2 | Ttl 2 | 2013-02-18 3 | Cat 1 | Ttl 3 | 2013-02-20 ``` What I like to do, is to get only one article by each category, and the one I will get must be the latest one in the table. In more depth, the result must looks like that: ``` ID | Category | Title | Date -------------------------------------- 2 | Cat 2 | Ttl 2 | 2013-02-18 3 | Cat 1 | Ttl 3 | 2013-02-20 ``` As you can see I have only one record by each category (one for Cat 1 and one for Cat 2) and among the records of the Cat 1 I have the latest. How can be translated that in MySQL query ?

Original source