fawzib fawzib - 7 months ago 19
SQL Question

ORDER BY rand() and group with group_id

I want to select rows in random order by sorted by

group_id
:

id group_id
1 1
2 1
3 2
4 2
5 3
6 4


I Would like to them to be sorted like below or randomized but still grouped:

id group_id
5 3
3 2
4 2
1 1
2 1
6 4


Below will sort them but i would like the sort to be random. How to achieve this?

SELECT * FROM table ORDER BY group_id DESC


Below is an example query I am figuring out for the actual application.

SELECT * FROM ( SELECT requests.*, accounts.username, accounts.password
FROM requests LEFT JOIN accounts ON requests.acc_id = accounts.id
WHERE requests.status NOT IN(1) AND accounts.status=1 ORDER BY RAND()
) AS subquery GROUP BY acc_id

Answer

You could try ordering by a random number with a seed dependent on the group_id, yet with a random factor. Like this:

SET @seed = 100*RAND();
SELECT * FROM table ORDER BY RAND(group_id*@seed);