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

However I am wondering whether there is a better more efficient way to achive this.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

For example by trying to combine the first two calls into

I am getting an invalid grouping error.Code:`SELECT field FROM table WHERE field>ROUND(RAND()*MAX(field)) ORDER BY field LIMIT 1`

Thanks!

Sorry, forgot to mention that this is about a MySQL database and hence probably went better into http://www.sitepoint.com/forums/forumdisplay.php?f=182