Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post

    ORDER BY specific values

    Hi guys!

    I am familiar with the ORDER BY part of a query, but it's my understanding that you can only order by ASC or DESC.

    I have a column of varchar datatype that holds the name of the 'source' the data in the row came from. For example, lets say there are 3 sources: Google, Bing, Yahoo.

    Is there a way I can specify which one to display first?
    ex. SELECT * FROM table ORDER BY 'Yahoo','Bing','Google'

    OR
    ex. SELECT * FROM table ORDER BY 'Google','Yahoo','Bing'

    Thanks
    Last edited by inchecksolution; 08-08-2011 at 09:04 PM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    ASC and DESC are basic sorts.

    MySQL has a great feature called ORDER BY FIELD

    Code:
    ORDER BY FIELD(source,'Google','Yahoo','Bing')
    That will work if those are the only values in the source field. If you also need to push those values to the top and then sort the rest alphabetically then you need to do this:

    Code:
    ORDER BY 
      CASE WHEN source IN ('Bing','Google','Yahoo') THEN 0 ELSE 1 END, 
      FIELD(source,'Google','Yahoo','Bing'),
      source
    what that does is
    1) sort Bing, Google, Yahoo to the top and all results beneath those.
    2) order Bing, Google, Yahoo in the order you wanted
    3) order the source field alphabetically for the rest of the results.

    If you use another database app where ORDER BY FIELD is not available you can specify all the ORDER BY with a CASE statement:

    Code:
    ORDER BY
      CASE 
        WHEN source='Google' THEN 1
        WHEN source='Yahoo' THEN 2
        WHEN source='Bing' THEN 3
        ELSE 4 END,
      source
    As you see not much different but if you had 7 or 8 items to order at the top, a little more time consuming than the FIELD allows you to do.
    Last edited by guelphdad; 08-08-2011 at 04:55 PM.

  • #3
    Regular Coder
    Join Date
    Jul 2011
    Location
    Toronto, ON
    Posts
    102
    Thanks
    12
    Thanked 1 Time in 1 Post
    Quote Originally Posted by guelphdad View Post

    Code:
    ORDER BY 
      CASE WHEN source IN ('Bing','Google','Yahoo') THEN 0 ELSE 1 END, 
      FIELD(source,'Google','Yahoo','Bing'),
      source
    Thanks for that - exactly what I was looking for - but I'm confused as to why they are written twice? What is the functino of the first and what is the function of the second? (IN ('Bing','Google','Yahoo') AND FIELD(source,'Google','Yahoo','Bing'))

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    IN replaces a list of OR conditions

    WHERE somecolumn='this' OR somecolumn='that' OR somecolumn='theotherthing'

    can be written as:

    WHERE somecolumn=('this','that','theotherthing')

    ORDER BY
    CASE WHEN source IN ('Bing','Google','Yahoo') THEN 0 ELSE 1 END,
    -- this line is necessary to sort Bing, Google and Yahoo to the top of the column and all other rows drop beneath them. Does that make sense?

    FIELD(source,'Google','Yahoo','Bing'),
    -- this line says when you come across Bing, Google, or Yahoo in the results you need to order them in this specific order. So those appear at the top of the column because of the first condition and now are ordered in the exact order of the second condition

    source

    -- and this says to order the rest of the column alphabetically.


  •  

    Posting Permissions

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