ORDER BY RAND()でのソート

質問

こんにちは。

id states user_id
----------------------
1 4 nakamura
2 2 yamada
3 5 yoshida
4 2 suzuki
まずstatesの順番をランダム表示にしたいのですが、同じstatesはまとめて表示したいのです。
つまり
id states user_id
----------------------
3 5 yoshida
2 2 yamada
4 2 suzuki
1 4 nakamura

このようになってくれるとありがたいのですが、どのようにしたらいいのでしょうか？
トライしたのは
SELECT * FROM table ORDER BY rand(),states
これだとランダムになるのですが、statesでの
ソートがうまくいきません。
GROUP BYみたいに一つにまとめてくれる機能があるといいんですが...。

よろしくおねがいします。

回答No.2

agricap

当てずっぽうです。手元にOracleがないので、正確にはわかりません。
エラーになるかもしれません。
SELECT A.* FROM table A,
　　(SELECT status, rand() as ran FROM (SELECT DISTINCT status FROM table)) B
WHERE A.status = B.status ORDER BY B.ran

回答No.1

yambejp

テンポラリつかっていいなら以下のような
感じでいけます。

SET @NUM=0;

CREATE TEMPORARY TABLE `TEMP1`
SELECT @NUM:=@NUM+1 AS `NUM`,`*` FROM `table` ORDER BY rand();

CREATE TEMPORARY TABLE `TEMP2`
SELECT * FROM `TEMP1` GROUP BY `states`
ORDER BY `NUM`;

SELECT `TEMP1`.`id`,`TEMP1`.`states`,`TEMP1`.`user_id`
FROM `TEMP2`
INNER JOIN `TEMP1` ON `TEMP1`.`states`=`TEMP2`.`states`
ORDER BY `TEMP2`.`NUM`;

順番にいうと、最初は変数の指定。
その次のsqlで順番をランダムにします。
そのとき順番をNUMというカラムで保持します。
次のsqlでstatesをユニークに抜き出します。
最後のsqlで、ユニークなテーブルにJOINして
しまえば、ご要望の形式になるかと。
常にORDER BY でNUMを引っ張ってやらないと
順番が保証されない様なので、こんなやり方になり
ますが、AUTO_INCREMENTとかする手もあります。
（その方が面倒なことになりそうですが）

副問合せでもいけるのかもしれませんが、この
フローであれば3.23で動きました。