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

Download the Scale-Out and High Availability whitepaper. Learn why leading enterprises choose the Couchbase NoSQL database over MongoDB™ after evaluating side by side.

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

The Forrester Wave™: Big Data NoSQL report. See how the top NoSQL providers stack up. Download now.

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