Paulund
2015-08-26 #mysql

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

UpChecker

Reliable uptime monitoring and instant alerts for any website downtime.

  • Uptime Monitoring
  • Performance Monitoring
  • SSL Certificate Alerts
  • Domain Monitoring
  • DNS Checker
  • XML Sitemap Monitoring