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

Question

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 ?

Solution 1

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)

Solution 2

You can use a WHERE clause to filter the data:

select *
from yourtable t1
where (category, date) in (select category, max(date)
                           from yourtable t2
                           group by category)

See SQL Fiddle with Demo