MySQL rand selection with group by! Help

Anon

Hi all,

I'm having a quite stupid MySQL problem. First look at the following:

mysql> select * from groups;
+---------+-------+------+-------+
| groupid | type | lang | categ |
+---------+-------+------+-------+
| 1 | frame | de | a |
| 1 | frame | de | b |
| 1 | frame | de | c |
| 1 | frame | de | d |
| 2 | frame | de | e |
| 2 | frame | de | f |
| 3 | frame | de | g |
+---------+-------+------+-------+
7 rows in set (0.00 sec)

So when I group the items by groupid I get the following result:

mysql> select * from bannerindex group by groupid;
+---------+-------+------+-------+
| groupid | type | lang | categ |
+---------+-------+------+-------+
| 1 | frame | de | a |
| 2 | frame | de | e |
| 3 | frame | de | g |
+---------+-------+------+-------+
3 rows in set (0.00 sec)

Now let's get to my problem. I want to select one row for different groupids each time I do the query.

If I'd select a random row from the topmost results I'd get - for example - a row for groupid 1 the first query, another one for groupid 1 again the next query, then maybe a row for groupid 2, one for id 1 again, maybe one for 3, ... the groupid usage is unbalanced.

But I'd like to select another random row per groupid each query. I hope you understand me and my lousy English 🙁

[deleted]

"the groupid usage is unbalanced"

So what you want is to select a random groupid, but with the same 'chance' for every value of groupid; 33% for 1, 33% for 2 and 33% for 3?

Then try something like this:

SELECT distinct(groupid)
FROM table
ORDER BY rand()
LIMIT 1;

Anon

So what you want is to select a random
groupid, but with the same 'chance' for
every value of groupid; 33% for 1, 33% for
2 and 33% for 3?

that's exactly what i want.

Then try something like this:
SELECT distinct(groupid)
FROM table
ORDER BY rand()
LIMIT 1;

unfortunately this is not doing the trick 🙁 i tested 1000 loops with:

select distinct(groupid),categ from bannerindex order by rand() limit 1;

and it returned these counts:

array(3) {
[1]=>
int(545)
[2]=>
int(292)
[3]=>
int(163)
}

as you can see the groupids (1,2,3) are far from being balanced :\

someone having any other ideas how to do that? tia, pete

[deleted]

Hmm...
in that case I'd go for a PHP-side solution, select the distinct groupid's, generate a random number between 0 and the number of result records, and fetch that row.

Not elegant, but effective.