T-SQL sorting

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    Comments posted to this topic are about the item T-SQL sorting

  • HappyGeek

    SSCoach

    Points: 18684

    Simple one to start the week, thanks Evgeny.

    ...

  • Alan Burstein

    SSC Guru

    Points: 61087

    I guessed correctly. Good question!

    "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

  • Toreador

    SSChampion

    Points: 11260

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

  • Carlo Romagnano

    SSC-Insane

    Points: 22009

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    There's not conflict!
    2 is an integer defining the column position, "2" is the name of the column.

  • call.copse

    SSCoach

    Points: 17238

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

  • sknox

    SSChampion

    Points: 12292

    call.copse - Monday, January 8, 2018 4:10 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

    While I disagree with the statement "ordinal position is a sensible choice occasionally in sorting", I'm open to the possibility that I've missed something.
    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?
    Note that I would not accept any situation which could be resolved simply by aliasing an expression or ambiguous column name, as providing clear column names would be the more sensible choice.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    Carlo Romagnano - Monday, January 8, 2018 3:31 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    There's not conflict!
    2 is an integer defining the column position, "2" is the name of the column.

    Quite correct.  To visualize this point, run the following code.


    select col1 as "2", col2 as "3", col3 as "1"
    from dbo.sortme
    order by 2;

    select col1 as "2", col2 as "3", col3 as "1"
    from dbo.sortme
    order by "2";

  • manik_anu

    SSCrazy

    Points: 2367

    nice start of this week...Thanks

    Manik
    You cannot get to the top by sitting on your bottom.

  • Sean Lange

    SSC Guru

    Points: 286536

    sknox - Monday, January 8, 2018 6:30 AM

    call.copse - Monday, January 8, 2018 4:10 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

    While I disagree with the statement "ordinal position is a sensible choice occasionally in sorting", I'm open to the possibility that I've missed something.
    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?
    Note that I would not accept any situation which could be resolved simply by aliasing an expression or ambiguous column name, as providing clear column names would be the more sensible choice.

    Nope. Not a single valid reason could exist where sorting by ordinal position actually makes sense. It is damned confusing when you read it and it is incredibly brittle. Consider that since an order by was added to the query that the order must be important. Now consider a long complex query in a stored procedure where a developer modifies the query and changes the column order in the select statement but doesn't realize they also MUST change the order by because the author built in a time-bomb. In fact, in the article referenced MS even states that while it is valid to use ordinal position it is NOT a good idea.

    Excellent question though!!

    _______________________________________________________________

    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

    SSC Guru

    Points: 997116

    sknox - Monday, January 8, 2018 6:30 AM

    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?

    I would never allow such a thing in "real" code but, I have used it to simplify "what if" code for experiments where I always want the sort column to be the first column in the return and I frequently change that column while investigating data or related code.  In other words, for research and experimental purposes only and only if there's an actual advantage to doing so.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • HappyGeek

    SSCoach

    Points: 18684

    sknox - Monday, January 8, 2018 6:30 AM

    call.copse - Monday, January 8, 2018 4:10 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

    While I disagree with the statement "ordinal position is a sensible choice occasionally in sorting", I'm open to the possibility that I've missed something.
    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?
    Note that I would not accept any situation which could be resolved simply by aliasing an expression or ambiguous column name, as providing clear column names would be the more sensible choice.

    No, no,no. There is no reason to do so and MS even advise against doing so!

    ...

  • Toreador

    SSChampion

    Points: 11260

    sknox - Monday, January 8, 2018 6:30 AM

    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?

    It can be clearer if you are using UNION statements. The ORDER BY goes right at the bottom, the alias used is the one from the first Select which will be right at the top, so you need to scroll back to find out what it's ordering by. If you use position then you can see it from the final Select.

    Of course you can achieve much the same thing just by repeating the aliases in each Select, which makes things even clearer!

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Sean Lange - Monday, January 8, 2018 8:31 AM

    sknox - Monday, January 8, 2018 6:30 AM

    call.copse - Monday, January 8, 2018 4:10 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

    While I disagree with the statement "ordinal position is a sensible choice occasionally in sorting", I'm open to the possibility that I've missed something.
    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?
    Note that I would not accept any situation which could be resolved simply by aliasing an expression or ambiguous column name, as providing clear column names would be the more sensible choice.

    Nope. Not a single valid reason could exist where sorting by ordinal position actually makes sense. It is damned confusing when you read it and it is incredibly brittle. Consider that since an order by was added to the query that the order must be important. Now consider a long complex query in a stored procedure where a developer modifies the query and changes the column order in the select statement but doesn't realize they also MUST change the order by because the author built in a time-bomb. In fact, in the article referenced MS even states that while it is valid to use ordinal position it is NOT a good idea.

    Excellent question though!!

    I must agree wholeheartedly.
    both about using ordinal position and quality of the question, thanks Evgeny.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Alan Burstein

    SSC Guru

    Points: 61087

    Stewart "Arturius" Campbell - Monday, January 8, 2018 10:08 AM

    Sean Lange - Monday, January 8, 2018 8:31 AM

    sknox - Monday, January 8, 2018 6:30 AM

    call.copse - Monday, January 8, 2018 4:10 AM

    Toreador - Monday, January 8, 2018 2:01 AM

    Quite interesting I suppose, though the URL in the explanation doesn't state what happens when there is a conflict between alias and position.
    The main thing I'd want to know though is the best retraining course I could send the developer on!

    I'd be thinking about the best way to find a new home for any developer that willfully introduced that much confusion to any query! Whilst ordinal position is a sensible choice occasionally in sorting the query as posted would do anyone's head in...

    While I disagree with the statement "ordinal position is a sensible choice occasionally in sorting", I'm open to the possibility that I've missed something.
    Can anyone come up with a situation where using ordinal position in the sort clause would be sensible?
    Note that I would not accept any situation which could be resolved simply by aliasing an expression or ambiguous column name, as providing clear column names would be the more sensible choice.

    Nope. Not a single valid reason could exist where sorting by ordinal position actually makes sense. It is damned confusing when you read it and it is incredibly brittle. Consider that since an order by was added to the query that the order must be important. Now consider a long complex query in a stored procedure where a developer modifies the query and changes the column order in the select statement but doesn't realize they also MUST change the order by because the author built in a time-bomb. In fact, in the article referenced MS even states that while it is valid to use ordinal position it is NOT a good idea.

    Excellent question though!!

    I must agree wholeheartedly.
    both about using ordinal position and quality of the question, thanks Steve.

    If there's any room left on this band wagon I would like to get on.

    "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

Viewing 15 posts - 1 through 15 (of 25 total)

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