Hi, I am having a table with a numeric column where several rows can contain the same value. Now I am trying to determine a random value and then to get a random row from all rows with the next larger value. Basically I already got it working with the following pseudo code // Getting a random value between 0 and the maximum value of field value = SELECT ROUND(RAND()*MAX(field)) FROM table // Getting the next larger value based on the random value value2 = SELECT field FROM table WHERE field>value ORDER BY field LIMIT 1 // Getting one random ...
Hi,
I am having a table with a numeric column where several rows can contain
the same value. Now I am trying to determine a random value and then to
get a random row from all rows with the next larger value.
Basically I already got it working with the following pseudo code
// Getting a random value between 0 and the maximum value of field
value = SELECT ROUND(RAND()*MAX(field)) FROM table
// Getting the next larger value based on the random value
value2 = SELECT field FROM table WHERE field>value ORDER BY field LIMIT 1
// Getting one random row from all rows with the determined value
row = SELECT * FROM table WHERE field=value2 ORDER BY RAND() LIMIT 1
However I am wondering whether there is a better more efficient way to
achive this.
For example by trying to combine the first two calls into
SELECT field FROM table WHERE field>ROUND(RAND()*MAX(field)) ORDER BY
field LIMIT 1
I am getting an invalid grouping error.
Thanks!
Bookmarks