T-SQL sorting

  • I agree that although use of column numbers is a no no for production code, this makes a witty QotD.

    Thanks, Evgeny!

  • Good question, but I agree with everyone saying that using the ordinal position for sorting is a poor choice.  Although I haven't done it, I can see Jeff's point in experimentation and testing.

  • I guess that I'll dissent.  There are certain very limited situations where sorting by ordinal position is useful.  Specifically, in development and troubleshooting, the columns in the SELECT statement may be in flux, especially when the final column that you're planning to sort by is a complex formula that you are in the process of creating/troubleshooting. In these cases, it may make sense to use an ordinal sort to test intermediate values and to guarantee repeatable results.  I would agree that you should never use an ordinal sort in production code.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Tuesday, January 9, 2018 1:07 PM

    I guess that I'll dissent.  There are certain very limited situations where sorting by ordinal position is useful.  Specifically, in development and troubleshooting, the columns in the SELECT statement may be in flux, especially when the final column that you're planning to sort by is a complex formula that you are in the process of creating/troubleshooting. In these cases, it may make sense to use an ordinal sort to test intermediate values and to guarantee repeatable results.  I would agree that you should never use an ordinal sort in production code.

    Drew

    I would agree with this because while doing development or troubleshooting we often don't use the same coding style as we do for production code. Shortcuts and kludges to reproduce the issue quickly is quite common. I would concede that in those cases I could see ordering by ordinal position. However, I would also make the case that is likely to introduce other challenges because as you stated, the column list may not be very solid yet and we could now be changing the order of the results completely accidentally. I will continue to use the column name even in those cases to avoid either accidentally messing myself up or worse, leaving the ordinal in the code on accident.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • drew.allen - Tuesday, January 9, 2018 1:07 PM

    I guess that I'll dissent.  There are certain very limited situations where sorting by ordinal position is useful.  Specifically, in development and troubleshooting, the columns in the SELECT statement may be in flux, especially when the final column that you're planning to sort by is a complex formula that you are in the process of creating/troubleshooting. In these cases, it may make sense to use an ordinal sort to test intermediate values and to guarantee repeatable results.  I would agree that you should never use an ordinal sort in production code.

    Drew

    I think everyone who has said "never" has been speaking in the context of production. At least that's how I interpreted it. 

    That said, I have never thought of a decent reason to sort that way. I've always stuffed my logic into a subquery or cte when doing things like sorting by a complex formula. Using the ordinal position seems way simpler. Excellent Post Drew.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Well, I have two problems with this QOTD.  Or perhaps I see two faults in the new "improved" website.

    1:  The select statement isn't there - it's a meaningless icon, no text.  So how is anyone to know what the result of the select statement is?  I guess this is a recent deterioration (maybe happened during the release of the new "improved" website).

    2.  How have I arrived at this page?   I haven't answered the question, and in the good old days people who hadn't answered a question couldn't see the comments on it.

    Tom

  • TomThomson wrote:

    Well, I have two problems with this QOTD.  Or perhaps I see two faults in the new "improved" website. 1:  The select statement isn't there - it's a meaningless icon, no text.  So how is anyone to know what the result of the select statement is?  I guess this is a recent deterioration (maybe happened during the release of the new "improved" website). 2.  How have I arrived at this page?   I haven't answered the question, and in the good old days people who hadn't answered a question couldn't see the comments on it.

    There's another problem, yet!  The link in the first post of this thread points to the wrong bloody QOTD for me!

    For me, it points to https://www.sqlservercentral.com/questions/t-sql/166240 , which is the wrong article.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    TomThomson wrote:

    Well, I have two problems with this QOTD.  Or perhaps I see two faults in the new "improved" website. 1:  The select statement isn't there - it's a meaningless icon, no text.  So how is anyone to know what the result of the select statement is?  I guess this is a recent deterioration (maybe happened during the release of the new "improved" website). 2.  How have I arrived at this page?   I haven't answered the question, and in the good old days people who hadn't answered a question couldn't see the comments on it.

    There's another problem, yet!  The link in the first post of this thread points to the wrong bloody QOTD for me! For me, it points to https://www.sqlservercentral.com/questions/t-sql/166240 , which is the wrong article.

     

    Same for me. This site is becoming nearly useless for what it was intended to do. The only I use it for anymore is to keep up with the people on the thread. Actually answering questions and helping people is just too frustrating because of the user hostile interface.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Jeff Moden wrote:

    There's another problem, yet!  The link in the first post of this thread points to the wrong bloody QOTD for me! For me, it points to https://www.sqlservercentral.com/questions/t-sql/166240 , which is the wrong article.

    You are right, that link is completely wrong.   The new version of SQLS_C_ really is somewhat broken, isn't it?

    Tom

  • Oooohhhh, don't get me started again, Tom. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply