Get Last Record in Each MySQL Group
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.
Join the DZone community and get the full member experience.
Join For FreeMariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.
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 6I 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_idBut 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 6The 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 6MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.
Like This Article? Read More From DZone
Published at DZone with permission of Paul Underwood , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}