Random order for group of rows in mysql

问题:

maybe it´s easy but i have no clue how to handle this correctly. I have the following table t1 with this data:

-----------------
| id    | gr_id |
-----------------
| 1     | a     |
| 2     | a     |
| 3     | b     |
| 4     | b     |
| 5     | c     |
| 6     | c     |
| 7     | d     |
| 8     | d     |
-----------------

I would like to get randomly gr_ids like this:

-----------------
| id    | gr_id |
-----------------
| 3     | b     |
| 4     | b     |
| 5     | c     |
| 6     | c     |
| 7     | d     |
| 8     | d     |
| 1     | a     |
| 2     | a     |
-----------------

Getting ordered gr_ids ascend and descend is pretty easy, but getting randomly grouped results, is pretty more complicated than i thought it is.

I do not get it, when i use GROUP BY or sth. similar i get for sure only one row each group. How can i randomly order groups, what is the trick here???

Thank you guys for bringing light into darkness ;)

回答1:

E.g.:

SELECT x.*
  FROM my_table x
  JOIN 
     ( SELECT DISTINCT gr_id
                     , RAND() rnd 
                  FROM my_table
     ) y
    ON y.gr_id = x.gr_id
 ORDER 
    BY y.rnd
     , x.id;

回答2:

ORDER BY RAND() should work for you.