union all and order by problemo

i need to select country_id =26 and then the rest i want them ordered by name.
if i put order by country_name, the country_id 26 isn't displayed as the first one.
is there anyway to apply the order by only to the second select not the whole?

SELECT *
FROM tblCountry
WHERE Country_Id = 26
UNION ALL
SELECT *
FROM tblCountry
WHERE Country_Id <> 26

--order by country_Name

You can use a CASE expression in your ORDER BY clause. This shows you how to order by a column Country_Name, after putting Country_Id 26 at the top of the list:

SELECT * FROM tblCountry
ORDER BY CASE Country_Id WHEN 26 THEN 0 ELSE 1 END, Country_Name

SELECT * FROM tblCountry<br />ORDER BY CASE Country_Id %26, Country_Name<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote><i>Originally posted by Roji. P. Thomas</i><br /><br />SELECT * FROM tblCountry<br />ORDER BY CASE Country_Id %26, Country_Name<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><font color="red">Server: Msg 170, Level 15, State 1, Line 3</font id="red"> <br /><br />[<img src='/community/emoticons/emotion-5.gif' alt='' />]<br />Aaah<br /><br />It should be <br /><br />SELECT * FROM tblCountry<br />ORDER BY Country_Id %26, Country_Name<br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

I hadn't seen the ORDER BY %26 syntax before. I don't see it in Books Online. Does anyone have a source for that? I ran a quick test on one of my tables with an integer key. I realize that you are using the additional Country_Name field, It looks like the rest of the column is not sorted. For example:

pkTypeId, Field2
260
271
280
290
31
41
300
310
320
330
141

Is there anything wrong with:
SELECT * FROM tblCountry
ORDER BY CASE WHEN Country_Id = 26 THEN '' ELSE Country_Name END

Or possibly:
SELECT * FROM tblCountry
ORDER BY NULLIF(Country_Name, 'USA')

If you're going to hard-code the ID you may as well hard-code the name instead.



Jason Akin
CUInterface, LLC

quote:Originally posted by ja928

I hadn't seen the ORDER BY %26 syntax before. I don't see it in Books Online. Does anyone have a source for that?

Look up "% modulo" in books online.

quote:
Is there anything wrong with:
SELECT * FROM tblCountry
ORDER BY CASE WHEN Country_Id = 26 THEN '' ELSE Country_Name END

If any of the rows has its CountryName as NULL, you wont get the desired result.

quote:
Or possibly:
SELECT * FROM tblCountry
ORDER BY NULLIF(Country_Name, 'USA')
If you're going to hard-code the ID you may as well hard-code the name instead.

This wont satisfy the original requirement.


Honestly, Even I think a CASE WHEN is the right approach there. I was just pointing out an alternative.

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Roji,

The problem with %26 is of course that it only says that rows with IDs 0, 26, 52, 78, 104, etc. come before all others. It doesn't force 26 to come first.

quote:Originally posted by Adriaan

Roji,

The problem with %26 is of course that it only says that rows with IDs 0, 26, 52, 78, 104, etc. come before all others. It doesn't force 26 to come first.

Good catch. Point taken.

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Thanks for clarifying, Roji. I was familiar with modulo, but hadn't seen it for an order by.

I realize that the original question was about IDs, but I was trying to think outside the box. Sometimes the solution isn't always the answer to the question asked. I meant to indicate the poster had a given country in mind, but hard-coding the id is as bad as hard-coding the name.

If the table is relatively small and static, another solution might be to add an ordinal column. If the list is customizable or business requirements change, it can be ordered once. If the business later wants to sort with two or three countries at the top, an ordinal column could be useful.

Jason Akin
CUInterface, LLC

quote:Originally posted by ja928
If the table is relatively small and static, another solution might be to add an ordinal column. If the list is customizable or business requirements change, it can be ordered once. If the business later wants to sort with two or three countries at the top, an ordinal column could be useful.

Completely agreed.

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

quote:The problem with %26 is of course that it only says that rows with IDs 0, 26, 52, 78, 104, etc. come before all others. It doesn't force 26 to come first.

To finish my half-complete observation: after this group, the next one is 1, 27, 53, etc. (x % 26 = 1) - then 2, 28, 54, etc. - then 3, 29, 55, etc. - etc. And of course these IDs will be grouped, but they won't necessarily be in any order within that group.

quote:Originally posted by Adriaan

quote:The problem with %26 is of course that it only says that rows with IDs 0, 26, 52, 78, 104, etc. come before all others. It doesn't force 26 to come first.

To finish my half-complete observation: after this group, the next one is 1, 27, 53, etc. (x % 26 = 1) - then 2, 28, 54, etc. - then 3, 29, 55, etc. - etc. And of course these IDs will be grouped, but they won't necessarily be in any order within that group.

From a technical point of view, Its very easy to solve that by

ORDER By Country_Id /26, CountryID % 26


Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Why so technical?

ORDER BY Country_id % 26, Country_Id

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Why so technical?<br /><br />ORDER BY Country_id % 26, Country_Id<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />