T-SQL sorting

Question

Post reply

T-SQL sorting

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

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

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: 442467 More actions · Answer 2

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: 76996 More actions · Answer 3

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: 286590 More actions · Answer 4

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: 61141 More actions · Answer 5

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

TomThomson SSC Guru Points: 104773 More actions · Answer 6

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: 1004401 More actions · Answer 7

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)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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.