MySQL Getting last 5 entries for each id in a SELECT - WHERE IN statement

Question

I would like to select last 5 entries for each of the id in a SELECT - WHERE IN statement.

//How to get last 5 entries for each id
SELECT * FROM table1
WHERE id IN (111,222,333,.....)
ORDER BY date DESC
LIMIT 5

EDIT: Example of how data structure

id  date    name
111 7-Nov   Anna
111 8-Nov   Belle
111 9-Nov   Mary
111 10-Nov  John
111 11-Nov  Robert
111 12-Nov  Gary
111 13-Nov  Rick
222 8-Apr   Sarah
222 9-Apr   Lee
222 10-Apr  Margaret
222 11-Apr  Lisa
222 12-Apr  Will
222 13-Apr  Alex
222 14-Apr  Kelly
222 15-Apr  Lucas

Desired results

id  date    name
111 13-Nov  Rick
111 12-Nov  Gary
111 11-Nov  Robert
111 10-Nov  John
111 9-Nov   Mary
222 15-Apr  Lucas
222 14-Apr  Kelly
222 13-Apr  Alex
222 12-Apr  Will
222 11-Apr  Lisa

I am not sure this can be done in mysql. I have tried looking at some answers posted and I can't find the answers.

http://stackoverflow.com/questions/8945538/mysql-query-a-list-of-values — Marco Mura, Dec 16 '14 at 08:11
@MarcoMura I am trying to get last 5 entries for each id (inside the where - in statement) now all the entries. — Cryssie, Dec 16 '14 at 08:14
why do you want to use a `WHERE IN()` statement when you want to get the last 5 entries? in your example you have to know the ids for the `IN()` condition - so you have to know the last 5 ids which you want to get. that doesn't make any sense. — low_rents, Dec 16 '14 at 08:20
http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — Zainul Abideen K T, Dec 16 '14 at 08:27

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

It just pseudo code, but I think it works. try this

SELECT

id,

SUBSTRING_INDEX(GROUP_CONCAT( some_column_1 ORDER BY date DESC),',',5)

SUBSTRING_INDEX(GROUP_CONCAT( some_column_2 ORDER BY date DESC),',',5)

....

FROM table WHERE id IN (111,222,333,.....) GROUP BY id;

it look not like you want. I just showing how use GROUP_CONCAT and SUBSTRING_INDEX function, you apply this, you can find answer.

score 0 · Answer 2 · answered Dec 16 '14 at 08:40

0

this doesn't make much sense, but if your mysql-version supports it, you can do it with a subquery:

SELECT * FROM `table1`
WHERE `id` IN (SELECT `id` FROM `table1` ORDER BY `date` DESC LIMIT 5)

answered Dec 16 '14 at 08:40

low_rents

4,161
2
20
47

MySQL Getting last 5 entries for each id in a SELECT - WHERE IN statement

2 Answers2