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 FreeRavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.
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 6Do you pay to use your database? What if your database paid you? Learn more with RavenDB.
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 }}