MySQL: limiting the number of results based on column value | Query aggregation

Question

I studied this problem, but I have problems finding a solution.

I have the following query that gives me a list of "some_id" s:

SELECT some_id FROM example GROUP BY some_id 

And I have the following query that will get a list of the last 5 entries for a row that has "some_id" equal to a number.

 SELECT * FROM example WHERE some_id = 1 ORDER BY last_modified DESC LIMIT 5 

How can I get the 5 most recent entries from the example table for each "some_id" using only one query? If there are less than 5 entries for "some_id", they can be included if this makes things less complicated.

Many thanks!

Answer 1

Found the answer by looking at the first answer in the following post:

How to limit the number of rows for a field value in SQL?

I changed it according to my specific needs:

 SELECT * FROM ( SELECT *, @num := if(@some_id = some_id, @num := @num + 1, 1) as row_num, @some_id := some_id as some_id FROM example ORDER BY last_modified DESC ) as e WHERE row_num <= 5 

Answer 2

Hi Please use Group by and having clause to check the limit for each id.

 SELECT * FROM `example` GROUP BY some_id HAVING count( * ) <= 5 ORDER BY last_modified DESC 

It will check every some_id and check if the number of rows for some_id is <=5 , this will be the result of the mapping.