A Special Order By clause?

The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical?

SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1
ORDER BY Country_Long

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

|||

You probably need to do 2 SELECTs and do a UNION. The first one will have 'United States' followed by the rest in alphabetical order.

|||

I read the question wrong

What CS4Ever and ndinakar both have posted is how you want to do this.

|||

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

|||

SolitaryMan:

Thanks all!!

Look at what this other awesome programmer came up with in addition to the winning example here:

SELECT * FROM COUNTRIES order by case country_long when 'United States' then 1 else 2 end asc, country_long

Your are welcome SolitaryMan.

Thanks for posting another example. Actullay there is always more than a way to do the thing.

I believe (not sure) the example in my previous post in better in term of performance, because of the CASE in the ORDER BY clause for the example you menioned. Since database engine will check the case of each record (long processing) while this is not the case in my example.

Again, thanks for sharing the example.

|||

CS4Ever:

I think this will work with you:

 
1SELECT 1,-- to keep a default value of county always on top (don't read it in your application)2 225,-- the value for U.S.3'United States'-- the default country (U.S. for example)45union67 SELECT 2,-- we need it to grantee the default value will be always up (you may use any value > 1)8 Country_ID,9 Country_Long10FROM Countries11WHERE isIndustrial = 112AND Country_ID <> 225-- Country_ID for the default county (in the first SELECT)13ORDER BY 1-- used to keep the default contry always in the top (first returned record)1415 GO
 

Good luck.

Change the order by clause to:

1ORDER BY 1, Country_Long

Good luck.