Illegal grouping

[some1]

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!

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

[r937]

try this to combine all three --

SELECT * 
  FROM yourtable 
 WHERE field
    >= ( select ROUND(RAND()*MAX(field)) 
           from yourtable
         ORDER BY field LIMIT 1 )
ORDER BY rand()  LIMIT 1

[some1]

Thanks very much, this should do the trick. I guess I should finally get used to the fact that MySQL now supports subqueries .

BTW, why didnt it like the MAX() in the WHERE clause?

[r937]

BTW, why didnt it like the MAX() in the WHERE clause?

because you can't have a non-aggregate expression in the SELECT if there's no GROUP BY

[some1]

Thanks again r937, the query was almost perfect, however it didnt only use all rows with the next larger value but the rows with all larger values. Hence I modified it with a third subquery and technically it should work fine.

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

The only problem I am encountering is that each third or fourth query returns an empty set. I am rather stumped, especially because the first subquery determining the next larger value always returns an existent value.

SELECT field
        FROM table
        WHERE field>
           (select ROUND(RAND()*MAX(field))
            from table)
        ORDER BY field LIMIT 1

Am I missing something?

[r937]

my query used >=, yours uses >

see if that makes a difference

[some1]

Unfortunately it doesnt (the frequency even seems to increase).

I might be wrong, but I think the two subqueries are fine and the error is somewhere in the main query. However I cant spot it.

[some1]

Nobody an idea?

Thanks!