SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: Illegal grouping
-
Jan 31, 2006, 03:25 #1
- Join Date
- Jan 2006
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Illegal grouping
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
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
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=182Last edited by some1; Jan 31, 2006 at 05:55.
-
Jan 31, 2006, 06:12 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
try this to combine all three --
Code:SELECT * FROM yourtable WHERE field >= ( select ROUND(RAND()*MAX(field)) from yourtable ORDER BY field LIMIT 1 ) ORDER BY rand() LIMIT 1
-
Jan 31, 2006, 06:27 #3
- Join Date
- Jan 2006
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jan 31, 2006, 06:37 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by some1
-
Feb 1, 2006, 04:00 #5
- Join Date
- Jan 2006
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Code: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.
Code:SELECT field FROM table WHERE field> (select ROUND(RAND()*MAX(field)) from table) ORDER BY field LIMIT 1
Last edited by some1; Feb 1, 2006 at 04:30.
-
Feb 1, 2006, 05:08 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
my query used >=, yours uses >
see if that makes a difference
-
Feb 1, 2006, 05:20 #7
- Join Date
- Jan 2006
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Feb 2, 2006, 04:06 #8
- Join Date
- Jan 2006
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Nobody an idea?
Thanks!
Bookmarks