How to get no more than two entries for each name in the where clause

Question

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

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

I understand that this request will not work as needed, but this is just an example. I can’t wrap my head around the situation.

My desired result:

id
1
5
4
8

Just to make this clear, LIMIT will return variable lines {limit}.

LIMIT 3 will return 3 rows in

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 1

Here's the request:

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 set the limit here.WHERE t.rank < LIMIT

---

SQL FIDDLE DEMO

---

By running the above request for example data, you will get the result as shown below:

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

Explanation:

1) name

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

:

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

2) @prevName , .

3) @nameRank . , . (, ).

4) :

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). , rank < 2

Answer 2

union :

(SELECT id FROM table WHERE name like 'Tom' LIMIT 2)
UNION
(SELECT id FROM table WHERE name like 'Tommy' LIMIT 2)

(.