T-SQL sorting

  • Revenant

    SSC-Forever

    Points: 42467

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

    Thanks, Evgeny!

  • Lynn Pettis

    SSC Guru

    Points: 442118

    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.

  • drew.allen

    SSC Guru

    Points: 76492

    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

  • Sean Lange

    SSC Guru

    Points: 286408

    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/

  • Alan Burstein

    SSC Guru

    Points: 61026

    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.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

  • TomThomson

    SSC Guru

    Points: 104763

    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

  • Jeff Moden

    SSC Guru

    Points: 994260

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Sean Lange

    SSC Guru

    Points: 286408

    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/

  • TomThomson

    SSC Guru

    Points: 104763

    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

  • Jeff Moden

    SSC Guru

    Points: 994260

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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