...

View Full Version : Resolved ORDER BY specific values



inchecksolution
08-08-2011, 04:24 PM
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 :)

guelphdad
08-08-2011, 04:51 PM
ASC and DESC are basic sorts.

MySQL has a great feature called ORDER BY FIELD



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:



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:



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.

inchecksolution
08-08-2011, 06:40 PM
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'))

guelphdad
08-08-2011, 09:16 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum