January 6, 2018 at 11:33 pm
Comments posted to this topic are about the item T-SQL sorting
January 6, 2018 at 11:34 pm
Simple one to start the week, thanks Evgeny.
...
January 7, 2018 at 5:26 pm
I guessed correctly. Good question!
-- Itzik Ben-Gan 2001
January 8, 2018 at 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!
January 8, 2018 at 3:31 am
Toreador - Monday, January 8, 2018 2:01 AMQuite 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.
January 8, 2018 at 4:10 am
Toreador - Monday, January 8, 2018 2:01 AMQuite 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...
January 8, 2018 at 6:30 am
call.copse - Monday, January 8, 2018 4:10 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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.
January 8, 2018 at 7:29 am
Carlo Romagnano - Monday, January 8, 2018 3:31 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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";
January 8, 2018 at 7:39 am
nice start of this week...Thanks
Manik
You cannot get to the top by sitting on your bottom.
January 8, 2018 at 8:31 am
sknox - Monday, January 8, 2018 6:30 AMcall.copse - Monday, January 8, 2018 4:10 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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/
January 8, 2018 at 8:32 am
sknox - Monday, January 8, 2018 6:30 AMCan 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
Change is inevitable... Change for the better is not.
January 8, 2018 at 8:45 am
sknox - Monday, January 8, 2018 6:30 AMcall.copse - Monday, January 8, 2018 4:10 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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!
...
January 8, 2018 at 9:29 am
sknox - Monday, January 8, 2018 6:30 AMCan 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!
January 8, 2018 at 10:08 am
Sean Lange - Monday, January 8, 2018 8:31 AMsknox - Monday, January 8, 2018 6:30 AMcall.copse - Monday, January 8, 2018 4:10 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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”
January 8, 2018 at 11:39 am
Stewart "Arturius" Campbell - Monday, January 8, 2018 10:08 AMSean Lange - Monday, January 8, 2018 8:31 AMsknox - Monday, January 8, 2018 6:30 AMcall.copse - Monday, January 8, 2018 4:10 AMToreador - Monday, January 8, 2018 2:01 AMQuite 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.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply