Houla Banada Houla Banada - 6 months ago 11
MySQL Question

How to get at most two entries for each name in the where clause

What I'm trying to accomplish is select multiple values with a limit.

SELECT id FROM table WHERE name IN ('Tom', 'Tommy') LIMIT 2


I understand this query won't work as needed, but it's just an example.
I can't quite wrap my head around on the situation

My desired output is:

id
1
5
4
8


Just to make it clear, LIMIT will return {limit} rows PER variable.

LIMIT 3 would return 3 rows per

id
1
5
6
4
8


Table:

id, name
1, Tom
2, Jeff
3, Jason
4, Tommy
5, Tom
6, Tom
7, Jeff
8, Tommy

Answer

Here's the query:

SELECT 
t.id,
t.name
FROM 
(
    SELECT 
    id,
    `name`,
    IF(@prevName = `name`, @nameRank := @nameRank + 1, @nameRank := 0) rank,
    @prevName := `name`
    FROM your_table, (SELECT @prevName := NULL, @nameRank := 0) var
    WHERE `name` IN ('Tom','Tommy')
    ORDER BY `name`,id
) t
WHERE t.rank < 2
ORDER BY t.id;

Note: You need to put the limit here WHERE t.rank < LIMIT


SQL FIDDLE DEMO


Running the above query on your sample data you will get an output like below:

| id |  name |
|----|-------|
|  1 |   Tom |
|  4 | Tommy |
|  5 |   Tom |
|  8 | Tommy |

Explanation:

1) First sort the data based on name in ascending order

    SELECT 
     id,
     `name`
    FROM your_table
    WHERE `name` IN ('Tom','Tommy')
    ORDER BY `name`

Result:

id  name
1   Tom
5   Tom
6   Tom
4   Tommy
8   Tommy

2) @prevName variable will track if the name is already seen before.

3) @nameRank variable will assign a rank number for each name. If the current name is already a seen one then it will assign an incremented number to it. (like roll number).

4) With the help of the above two variables the intermediate table would look like below:

id  name   rank
1   Tom     0 (seen first time so rank = 0)
5   Tom     1 (seen second time so rank = rank + 1 ; rank = 1)
6   Tom     2 (seen third time so rank = 2)
4   Tommy   0 (seen first time so rank = 0)
8   Tommy   1 (seen second time so rank = 1)

5) Since you want at most two entries per name so that you need to filter these data based on rank < 2