Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Get Last Record in Each MySQL Group

DZone's Guide to

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.

· Database Zone ·
Free Resource

RavenDB 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 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

Do you pay to use your database? What if your database paid you? Learn more with RavenDB.

Topics:
database ,sql ,mysql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}