CODEWITHSUNDEEP
mysqlsqlselectsubquerygreatest-n-per-group
Soheil
Soheil

Reputation: 627

mysql-select a random row from each id

I have a table in my db which has 2 columns: id and color. each id may have multiple rows with different values for color. so for example:

id     color
--------------
1      black
1      white
1      green
2      yellow
3      red
3      black

I want to select only one row for each id, but randomly. I have already tried to use two select queries, but it always returns the first row of each id. what is the problem?!

SELECT * FROM (SELECT * FROM collections ORDER BY RAND()) AS a
GROUP BY id

Upvotes: 0

Views: 800

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can try:

select t.*
from t
where t.color = (select t2.color
                 from t t2
                 where t2.id = t.id
                 order by rand()
                 limit 1
                );

For performance, you can try an index on (id, color).

Your code should simply not work. It uses select * with group by -- meaning that you have unaggregated columns. That should be a compile-time error.

EDIT:

LOL. Of course, the above has an issue. The subquery gets called for each row, giving each row an opportunity to be in the result set. Sigh. Sometimes code doesn't do what I want it to do. One solution is to seed the random number generator. This is "arbitrary" but not "random" -- you'll get the same values on each run:

select t.*
from t
where t.color = (select t2.color
                 from t t2
                 where t2.id = t.id
                 order by rand(concat(t2.id, t2.color))
                 limit 1
                );

If you don't have too many colors, you can use a group_concat() trick:

select t.id,
       substring_index(group_concat(color order by rand()), ',', 1)
from tA quick and dirty solution is to seed the random number generator:
group by id;

Upvotes: 1

Related Questions