#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171

    Is it possible to ORDER BY (id = a given value) ? Or have to use 2 queries?


    Hello;
    How is it possible to put the row with id 530 first, then order by name? Something like this:
    Code:
    SELECT id,
           name
    FROM   packages
    ORDER  BY ( id = 530 ),
              name;
    Thank you
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,118
    Rep Power
    1990
    I'm not sure if any of the gurus on here can do better but here's how I've done it before...

    Code:
    SELECT
        *, 
        IF(id=530,1,0) AS check_id
    FROM packages
    ORDER BY check_id DESC, name ASC
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    or you can do like this

    Code:
    SELECT id
          ,name
    FROM   packages
    ORDER  BY case when id = 530 then 'first' else 'second' end
             ,name;
    Last edited by TonyF123; April 19th, 2013 at 12:03 PM.
  6. #4
  7. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171
    Originally Posted by TonyF123
    or you can do like this

    Code:
    SELECT id
          ,name
    FROM   packages
    ORDER  BY case when id = 530 then 'first' else 'second' end
             ,name;
    This is brilliant. Would you please direct me to the source of this. Or tell me what to google for in order to read about this. I could find anything with "MySql ORDER BY given value".

    Thanks
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    37
    I would probably have got it from this forum, one of Rudy's (r937) many hints and tips.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by TonyF123
    ... one of Rudy's (r937) many hints and tips.
    i would've used humpty and dumpty
    Code:
    ORDER  
        BY CASE WHEN id = 530 
                THEN 'humpty'
                ELSE 'dumpty' END DESC
         , name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171
    Originally Posted by r937
    i would've used humpty and dumpty
    Code:
    ORDER  
        BY CASE WHEN id = 530 
                THEN 'humpty'
                ELSE 'dumpty' END DESC
         , name
    Page 177 SimplySQL . Great.

IMN logo majestic logo threadwatch logo seochat tools logo