1

Have a table like this:

| col1 | col2 |
---------------
|  1   |  a   |
|  2   |  b   |
|  3   |  a   |
|  4   |  a   |
|  5   |  a   |
|  6   |  b   |
|  7   |  a   |
|  8   |  b   |

I would like to select rows randomly that have a and b but limit the result to 2 rows for each a and b, like this:

| col1 | col2 |
---------------
|  3   |  a   |
|  5   |  a   |
|  2   |  b   |
|  8   |  b   |

I'm probably missing something, but I cannot figure out how to do it.

Arpi
  • 25
  • 3

2 Answers2

0

Is that what you want ? 

select * from yourTable where col2 = 'a' order by rand() limit 2
union all
select * from yourTable where col2 = 'b' order by rand() limit 2;
Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
  • add ORDER BY RAND() before the LIMIT to get random rows returned. – justAnotherUser Feb 19 '15 at 16:38
  • Something like this but i might have `c` or `d` to select also, should I just use unions or is there a better way? – Arpi Feb 19 '15 at 16:41
  • @Arpi you could do it using variables, see this post http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results . Else the easiest is to use union if you don't have many datas. Note that I changed union to union all for better performance as we already know there won't be duplicates. – Jean-François Savard Feb 19 '15 at 16:48
  • I will use union then, since it's much simpler than with variables. I might have max. 5 unions, which should not be a performance problem. Thank you! – Arpi Feb 19 '15 at 16:52
0

you need also to use ORDER BY RAND():

select * from yourTable where col2 = 'a' order by rand() limit 2
union
select * from yourTable where col2 = 'b' order by rand() limit 2;
void
  • 7,760
  • 3
  • 25
  • 43