SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Non-Member
    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
    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
    Code:
    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
    Last edited by some1; Jan 31, 2006 at 05:55.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by some1
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    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
    Am I missing something?
    Last edited by some1; Feb 1, 2006 at 04:30.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    my query used >=, yours uses >

    see if that makes a difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    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.

  8. #8
    Non-Member
    Join Date
    Jan 2006
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nobody an idea?

    Thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •