CODEWITHSUNDEEP
phpmysqlsorting
fawzib
fawzib

Reputation: 842

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

Upvotes: 3

Views: 670

Answers (3)

spencer7593
spencer7593

Reputation: 108530

I'm not understanding why the other answers are as involved as they are.

If you need the rows in a random order, just do ORDER BY RAND().

SELECT t.id
     , t.group_id
  FROM `table` t
 ORDER BY RAND()

Note that RAND() isn't truly random, it's a pseudo-random generator.

I'm not understanding why you need an inline view. You can perform the GROUP BY operation without an inline view. Given the GROUP BY, there's no need to do an unnecessary ORDER BY operation in the inline view.

 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
  GROUP
     BY acc_id
  ORDER
     BY RAND()

Upvotes: 0

Barmar
Barmar

Reputation: 782785

You could use a subquery that randomly orders all the group IDs and assigns a position to them with a user variable. Then join that with the original table and order it by the position.

SELECT t1.id, t1.group_id
FROM yourTable AS t1
JOIN (SELECT group_id, @position := @position + 1 as position
      FROM (SELECT DISTINCT(group_id) AS group_id
            FROM yourTable
            ORDER BY RAND()) AS x
      CROSS JOIN (SELECT @position := 0) AS var) AS t2
ON t1.group_id = t2.group_id
ORDER BY position, id

DEMO

Upvotes: 0

Te Ko
Te Ko

Reputation: 768

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);

Upvotes: 4

Related Questions