#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    1
    Rep Power
    0

    Query that returns a random row


    hi guys, i am trying to get a random row to be returned from the following query:

    SELECT username FROM userinfo it WHERE EXISTS (

    SELECT user_id FROM table1 t1
    INNER JOIN Table2 USING(user_id)
    INNER JOIN Table3 USING(user_id)
    INNER JOIN Table4 USING(user_id)
    INNER JOIN Table5 USING(user_id)
    INNER JOIN Table6 USING(user_id)
    INNER JOIN Table7 USING(user_id)
    INNER JOIN Table8 USING(user_id)
    INNER JOIN Table9 USING(user_id)
    INNER JOIN Table10 USING(user_id)
    WHERE t1.user_id = it.user_id
    );

    i tried adding ORDER BY rand() LIMIT 1 but i get an error. i was wondering if someone can let me know how i can get only one random row to be returned. thanks for your help.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by always_learning
    i tried adding ORDER BY rand() LIMIT 1 but i get an error.
    gee, i wonder if it would be possible for you to show us where you added it, and let us know what that error message was

    also, i'm just curious, what is the purpose of having all those INNER JOINs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo