SQL: Get 2 rows from each category

Question

I have a table with different categories. Is it possible to return two random rows from each category?

My table:

-----------------------------
| ID | CATEGORY             |
-----------------------------
| 1  | PINK                 |
| 2  | GREEN                |
| 3  | PINK                 |
| 4  | GREEN                |
| 5  | BLUE                 |
| 6  | BLUE                 |
| 7  | BLUE                 |
| 8  | PINK                 |
| 9  | GREEN                |
-----------------------------

What I want to output:

-----------------------------
| ID | CATEGORY             |
-----------------------------
| 1  | PINK                 |
| 8  | PINK                 |
| 2  | GREEN                |
| 4  | GREEN                |
| 6  | BLUE                 |
| 7  | BLUE                 |
-----------------------------

Yes . it is possible http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql — zod, Jul 19 '13 at 19:38
The random part isnt as important. I was really looking for getting 2 rows from each category. — user2217162, Jul 19 '13 at 19:42

score 0 · Accepted Answer · answered Jul 19 '13 at 19:55

0

select distinct c1.ID, c2.category
from mytable c1
join mytable c2 ON c1.category = c2.category and c1.ID <> c2.ID
group by c1.category, c2.ID;

answered Jul 19 '13 at 19:55

udog

1,490
2
17
30

This works, but lets say I want to get 5 rows from each category, would I have to join 5 tables? – user2217162 Jul 19 '13 at 20:07
You would need an additional self-join, and group by ID, for each increase in rows returned. For example, to get 3 of each category: select distinct c1.ID, c2.category from country c1 join country c2 ON c1.category = c2.category and c1.ID <> c2.ID join country c3 ON c2.category = c3.category and c1.ID <> c3.ID group by c1.category, c2.ID, c3.ID; – udog Jul 19 '13 at 20:20
Hey there user2217162, either of these answers acceptable to you? – udog Jul 22 '13 at 20:36

score 0 · Answer 2 · answered Jul 19 '13 at 20:18

Here is a way to get two random rows from each category:

select t.*
from t join
     (select t.category, substring_index(group_concat(id order by rand()), ',', 2) as ids
      from t
      group by category
     ) tc
     on find_in_set(t.id, tc.ids) > 0;

It uses group_concat() to put the ids into a list in random order, chooses the first two, and goes back and finds the rows with these ids. It is easily generalizable to more than 2 ids.

SQL: Get 2 rows from each category

2 Answers2