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