Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Order By CASE RAND()

    Realizing that using Rand in order by is a bad habit. Two questions:

    1. What is the best way to get random records?

    2. In the query below RAND() is not producing random results but sorts the results in some manor.

    Here is the URL: https://www.artcardsforcauses.com/ga....php?artists=0

    SELECT *, country_art
    FROM image_img
    LEFT JOIN label ON id_lbl=(theme_img
    OR style_img
    OR medium_img
    OR season_img)
    LEFT JOIN artist_art On artist_img=user_art
    WHERE approvedIMG=1
    AND (private_img <>1
    OR `private_img`IS NULL )
    AND ((0 =0)
    OR (artist_img = 0))
    ORDER BY CASE
    WHEN artist_img=0 Then RAND()
    WHEN artist_img=0 Then cdates_img
    END


    Thanks
    Nick

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Nick,

    have you tried something along these lines (it might not be the most efficient way but it will work):

    Code:
    SELECT *
    FROM   (SELECT fields,
                   Rand() AS random
            FROM   a1) a
    ORDER  BY a.random;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj View Post
    What is the best way to get random records?
    there is no best way, it depends on your situation

    there are many techniques discussed here --
    ~jk ORDER BY RAND()

    by the way, this --
    Code:
    id_lbl=(theme_img 
    OR style_img 
    OR medium_img 
    OR season_img)
    is invalid sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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