Hi Alexander,
Very weird! I tried the query myself, setting it up as such:
CREATE TABLE TEMP (id int not null AUTO_INCREMENT primary_key);
and inserted 18 values into it.
I am amazed that:
SELECT * FROM temp WHERE id=(
(SELECT id FROM temp WHERE id <
(SELECT ROUND(RAND()*MAX(id)) from temp)
ORDER BY id LIMIT 1));
sometimes gives more than 1 result, other times gives an empty set. I
would guess that it gives empty set when the random number picks the
maximum number, but in practice it seems to happen more often than
that. Plus, when I tried
SELECT * FROM temp WHERE id =
(SELECT id FROM temp WHERE id >
(SELECT ROUND(RAND()*(MAX(id)-1)) from temp)
ORDER BY id LIMIT 1) ORDER BY RAND() LIMIT 1;
Empty set (0.00 sec)
so even getting the max minus 1 didn't help.
I have no idea why that would happen, but I can confirm you're not crazy.
-Sheeri
On 2/2/06, Alexander Mueller <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I am trying to get the following query in MySQL 4.1 to return all rows with
> the next larger value of the one generated by RAND()*MAX(field) and then to
> randomly choose a row from these.
>
> SELECT *
> FROM table
> WHERE field=
> (SELECT field
> FROM table
> WHERE field>
> (select ROUND(RAND()*MAX(field))
> from table)
> ORDER BY field LIMIT 1)
> ORDER BY RAND() LIMIT 1
>
> So far it also seems to work, however every third or fourth run results in
> an empty result set and I do not really know why, especially because the
> first subquery always returns an existent value.
>
> SELECT field
> FROM table
> WHERE field>
> (select ROUND(RAND()*MAX(field))
> from table)
> ORDER BY field LIMIT 1
>
> I would appreciate any comment or suggestion.
>
> Thanks,
> Alexander
>
> --
> Lust, ein paar Euro nebenbei zu verdienen? Ohne Kosten, ohne Risiko!
> Satte Provisionen für GMX Partner: http://www.gmx.net/de/go/partner
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
