Get Last Record in Each MySQL Group

In this tutorial we will look at how you can use MySQL at getting the last record in a Group By of records.

For example if you have this result set of posts:

id category_id post_title
-------------------------
1 1 Title 1
2 1 Title 2
3 1 Title 3
4 2 Title 4
5 2 Title 5
6 3 Title 6

I want to be able to get the last post in each category which are Title 3, Title 5 and Title 6. To get the posts by the category you will use the MySQL Group By keyboard.

select * from posts group by category_id

But the results we get back from this query is.

id category_id post_title
-------------------------
1 1 Title 1
4 2 Title 4
6 3 Title 6

The group by will always return the first record in the group on the result set.

SELECT id, category_id, post_title
FROM posts
WHERE id IN (
 SELECT MAX(id)
 FROM posts
 GROUP BY category_id
);

This will return the posts with the highest IDs in each group.

id category_id post_title
-------------------------
3 1 Title 3
5 2 Title 5
6 3 Title 6