SQL Server Performance

union all and order by problemo

Discussion in 'General Developer Questions' started by mariechristine, Aug 16, 2006.

  1. mariechristine New Member

    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
  2. Adriaan New Member

    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
  3. Roji. P. Thomas New Member

    SELECT * FROM tblCountry<br />ORDER BY CASE Country_Id %26, Country_Name<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<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 />
  4. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><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 /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. Roji. P. Thomas New Member

    <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 />
  6. ja928 New Member

    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
  7. Roji. P. Thomas New Member

    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
  8. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Honestly, Even I think a CASE WHEN is the right approach there. I was just pointing out an alternative.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I think that's a cool trick for the lazycoder. Will definitely have to keep that in mind for future reference. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. Adriaan New Member

    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.
  10. Roji. P. Thomas New Member

    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
  11. ja928 New Member

    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
  12. Roji. P. Thomas New Member

    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
  13. Adriaan New Member

    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.
  14. Roji. P. Thomas New Member

    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
  15. Adriaan New Member

    Why so technical?

    ORDER BY Country_id % 26, Country_Id
  16. Roji. P. Thomas New Member

    <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 />

Share This Page