MySQL: Limiting number of results received based on a column value | Combining queries

greatest-n-per-group, mysql, sql

Solution

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

How do I limit the number of rows per field value in SQL?

I've modified it to fit 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

Problem

I've done research on this problem, but am having trouble 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 5 most recent 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 the top 5 most recent entries from the table "example" for each "some_id", using only one query? If there are less than 5 entries for a "some_id", it is okay to include them, if that makes things less complex. Many thanks!

Original source

Related problems