Sporadically empty result set

[Alexander Mueller]

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]