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

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

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