SQL Server Performance

Help on Order By !

Discussion in 'General Developer Questions' started by Bredsox, Aug 1, 2006.

  1. Bredsox New Member


    Hi All,
    I have a requirement which need to order by in certain way. For example, i have a list of columns which selects from joining multiple tables but one column needs to be sorted as follows:

    ID
    5
    10
    6
    7
    8
    Instead Of
    ID
    5
    6
    7
    8
    10.


    Any help would be great.

    Thanks
    Dan
  2. Luis Martin Moderator

    Could you tell about datatype of ID column?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Bredsox New Member

    Hi LuisMartin,
    It's an Integer column. Thanks
  4. Luis Martin Moderator

    I think is better for us if you post your script, because according datatype you have to have what you want.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  5. Bredsox New Member

    Here is the code sample.
    SELECT
    pe.login_name,
    we.application_id,
    we.win_export_id,
    we.enrollment_status_id,
    es.status_name,
    p.packet_id,
    p.name [packet_name],
    we.date_created,
    we.date_modified
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id

    This query returns multiple number of records with multiple Package_IDs including
    (5
    6
    7
    8
    10)
    associated with that records which need to be sorted as
    5
    10
    6
    7
    8

    Thanks
    Dan
  6. Luis Martin Moderator

    Sorry, I read the original post in wrong way.
    You have to convert int to vchar to get what you want, because what you get now is integer sort.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  7. Bredsox New Member

    Hi LuisMartin,
    Sorry there is a misunderstanding here. My question here was how can I sort that way for Packet_ID? I know we can sort in ASC or DESC order but it should be sorted as specified order which I can't get to figure it out. Thanks

    Packet_ID
    5
    10
    6
    7
    8

    Dan
  8. Bredsox New Member

    I think I got it. I added an additional column which sorts these ids based on case statement. Thanks for your time LuisMartin.

    Dan
  9. Adriaan New Member

    Dan,

    To be honest, that's really an unordered order.

    The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.
  10. 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 />To be honest, that's really an unordered order.<br />The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sorry.. Could you please explain what you meant by that <img src='/community/emoticons/emotion-5.gif' alt=';)' /> ?<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  11. Adriaan 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 Roji. P. Thomas</i><br /><br /><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 />To be honest, that's really an unordered order.<br />The IDs are probably ordered by another column in the table, or by a column in the table referenced by one of the FKs on your table.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Sorry.. Could you please explain what you meant by that <img src='/community/emoticons/emotion-5.gif' alt=';)' /> ?<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">What I meant was that perhaps the order in which these IDs are shown, which is not in any recognizable order <img src='/community/emoticons/emotion-5.gif' alt=';)' />, reflects the order of a different column. Perhaps the ID is the first column of the resultset, and if the original poster would check the other columns he might see a column that IS ordered in a recognizable fashion.
  12. Roji. P. Thomas New Member

    OP Said<br /> " I added an additional column which sorts these ids based on case statement"<br /><br />I Interpret this as<br /><br />CREATE TABLE #Test(id int)<br />INSERT INTO #Test VALUES(5)<br />INSERT INTO #Test VALUES(10)<br />INSERT INTO #Test VALUES(6)<br />INSERT INTO #Test VALUES(7)<br />INSERT INTO #Test VALUES(<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />SELECT Id, CASE id WHEN 5 THEN 1<br /> WHEN 10 THEN 2<br /> ELSE id END AS SortCol<br />FROM #Test<br />ORDER BY SortCol<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  13. Adriaan New Member

    Well, no need to add it as a column in the select list: putting the CASE statement itself in the ORDER BY clause will do the job.

    It's still an arbitrary order, in that there is no 'trend' in the order of the numbers. So yes, you can reproduce this exact sequence of numbers with a CASE statement, but you shouldn't call it an ordered resultset.
  14. FrankKalis Moderator

    The logic how the sorting is done isn't really clear (at least to me, anyway) [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />But I would have guessed from the narrative this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE #Test(id int, sortCol INT)<br />INSERT INTO #Test VALUES(5, 1)<br />INSERT INTO #Test VALUES(10, 2)<br />INSERT INTO #Test VALUES(6, 3)<br />INSERT INTO #Test VALUES(7, 4)<br />INSERT INTO #Test VALUES(8, 5)<br /><br />SELECT id<br /> FROM #test<br /> ORDER BY sortCol<br />DROP TABLE #Test<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  15. Roji. P. Thomas New Member

    <br />Cant resist <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Only the query that I posted satisfies the following statement.<br /><br />&lt;quote&gt;<br />I <b>added</b> an <b>additional column </b> which sorts these ids based on <b>case statement</b><br />&lt;/quote&gt;<br /><br />[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  16. FrankKalis Moderator

    ...and what is this supposed to teach me?

    Frank, you should read more closely through the question AND the thread, before posting unqualified answers. [XX(]

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  17. Bredsox New Member

    Thanks all for your comments and suggestions. I know we can sort as ASC or DESC but the FE wanted to be this way specifically (you can say hardcoded). I think Roji got the right solutions in this case, [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]. And this is how I did it. <br /><br />SELECT <br />pe.login_name, <br />we.application_id, <br />we.win_export_id, <br />we.enrollment_status_id, <br />es.status_name, <br />p.packet_id, <br />p.name [packet_name], <br />we.date_created, <br />we.date_modified,<br />CASE p.Packet_ID<br />WHEN 10 THEN 6<br />ELSE p.Packet_ID <br />END AS SortedPacketID<br />FROM Table1 we <br />JOIN Table2 ap ON we.application_id = ap.application_id <br />JOIN Table3 pe ON ap.person_id = pe.person_id <br />LEFT JOIN Table4 es <br />ON we.enrollment_status_id = es.enrollment_status_id <br />LEFT JOIN Table5 p ON we.packet_id = p.packet_id<br />ORDER BY SortedPacketID ASC<br /><br /><br />IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks<br /><br />Dan<br />
  18. Roji. P. Thomas New Member

    quote:Originally posted by Bredsox
    IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

    Try this.

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY PacketID % 5 ASC


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  19. Bredsox New Member

    Roji,
    It's not giving me the right order. This what your script is generating:

    ID
    10
    6
    7
    8
    5


    Thanks
    Dan
  20. Adriaan New Member

    I already mentioned that you don't have to put the CASE expression in the select list. Just use it as the ORDER BY clause.
  21. Bredsox New Member

    Adriaan,<br /> Can you give me an example of how can I put in Order By with that specific requiremnt (4 10 6 7 <img src='/community/emoticons/emotion-11.gif' alt='8)' />? ASC/DESC? It won't work because I want 4 10 6 7 8 INSTEAD OF (4 6 7 8 10 ASC OR 10 8 7 6 4 DESC).<br /><br /><br />Dan
  22. aefager New Member

    quote:Originally posted by Bredsox

    Roji,
    It's not giving me the right order. This what your script is generating:

    ID
    10
    6
    7
    8
    5


    Thanks
    Dan
    How would it give a 5 if the original was a 4?

    Your values, 4, 10, 6, 7, 8 do have a pattern, even though they are not intuitive. they would sort if the result was allowed to be for any n (n + 1) div 5.

    That would be
    (4 + 1) div 5 = 0
    (10 + 1) div 5 = 1
    (6 + 1) div 5 = 2
    (7 + 1) div 5 = 3
    (8 + 1) div 5 = 4

    If div does not work, it can be done as (n + 1) - (5 * int((n+1)/5), I guess.

    So, in this sample,
    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15

    would sort out as

    4, 9, 14, 5, 10, 15, 1, 6, 11, 2, 7, 12, 3, 8, 13

    Is that what you are looking for? Without a pattern, we are all trying to just gess how to get a quick sort to work for you...
  23. Bredsox New Member

    Hi Aefager,
    Your logic works pretty cool and certainly a good reference for me. In this case, they don't want the result to be like that. They just wanted hardcoded like 4 and then 10 and 6 and 7 and 8 and so on. sorry for the confusion.
    The actual data should be sorted as 4 10 6 7 8. 5 was just an example and actually with that example Roji's sorting without additional column worked but when I ran that script in the real data (4 6 7 8 10...) it did not give me 4 in the first place instead it gave me in the last place like this: 10 6 7 8 4. I just wanted to check if it can be done without additional column, otherwise it is already been deployed and looks like working for FE application. Thanks


    Dan
  24. Bredsox New Member

    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by Bredsox
    IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

    Try this.

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY PacketID % 5 ASC


    Roji. P. Thomas
    http://toponewithties.blogspot.com


    What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.
  25. Roji. P. Thomas New Member

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY (PacketID+1) % 5 ASC


    quote:Originally posted by Bredsox


    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by Bredsox
    IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

    Try this.

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY PacketID % 5 ASC


    Roji. P. Thomas
    http://toponewithties.blogspot.com


    What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  26. Bredsox New Member

    quote:Originally posted by Roji. P. Thomas

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY (PacketID+1) % 5 ASC


    quote:Originally posted by Bredsox


    quote:Originally posted by Roji. P. Thomas


    quote:Originally posted by Bredsox
    IF there is a way to sort this column without adding additional column in the select list, please post it here. Thanks

    Try this.

    SELECT * --Use Column list
    FROM Table1 we
    JOIN Table2 ap ON we.application_id = ap.application_id
    JOIN Table3 pe ON ap.person_id = pe.person_id
    LEFT JOIN Table4 es
    ON we.enrollment_status_id = es.enrollment_status_id
    LEFT JOIN Table5 p ON we.packet_id = p.packet_id
    ORDER BY PacketID % 5 ASC


    Roji. P. Thomas
    http://toponewithties.blogspot.com


    What do I change if the numbers are 4 6 7 8 10... ? to get 4 10 6 7 8.


    Roji. P. Thomas
    http://toponewithties.blogspot.com



    Worked like a charm but can you please explain how it sorted that way. What does the (Packet_ID+1) % 5 ASC is doing? Thanks Roji and everyone for your time.

    Dan
  27. Madhivanan Moderator

    % is a modulus operator. Read about it in sql server help file

    Madhivanan

    Failing to plan is Planning to fail
  28. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Your values, 4, 10, 6, 7, 8 do have a pattern, even though they are not intuitive. they would sort if the result was allowed to be for any n (n + 1) div 5.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Any finite list of numbers has a pattern/function that can be expressed using polinom. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  29. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Any finite list of numbers has a pattern/function that can be expressed using polinom. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Now this is called a mathematician's show-off.[<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<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>
  30. mmarovic Active Member

    Uh, old reflexes haven't died yet. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  31. FrankKalis Moderator

    Hey, looks like someone else also got nothing better to do than surf the net on a Sunday evening. [<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>

Share This Page