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

Question

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:

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

LIMIT 3 would return 3 rows per

Table:

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

So your requirement is : you want at most two entries for each name? — 1000111, May 29 '16 at 06:13

1000111 · Accepted Answer · 2016-05-29T07:08:15.130

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

Wajih · Answer 2 · 2016-05-29T06:49:50.037

0

Why you do not use union like this:

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

Be careful to the ( parentheses.

edited May 29 '16 at 06:49

answered May 29 '16 at 06:43

Wajih

4,227
2
25
40

Need an `Order by name,id` at the end – Déjà vu May 29 '16 at 06:53
Also you need one select query for each user in `WHERE IN (.....)` clause and later unionize them. – 1000111 May 29 '16 at 06:58
No you don't need an `Order By` clause because you already put the names in the order you want. @ringø – Wajih May 29 '16 at 07:01
If you have a lot of `name` that you want to `union` them then you can build a `procedure` or build the query in the language you use like `PHP`. – Wajih May 29 '16 at 07:02
1

Well in this case you need at least an `order by id` in each of the queries or the IDs may come in a different order (OP suggests IDs are ordered ASC for each name) – Déjà vu May 29 '16 at 07:04

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

2 Answers2