in Web Development

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

Upgrade to access all content on Paulund

Members unlock all tutorials and snippets

Access to all downloadable content

Access to code examples before others

Sign Up Now

Already a member? Login here

Subscribe To The Weekly Newsletter

Get weekly updates to your email