November 14, 2011 at 4:12 am
Comments posted to this topic are about the item Stairway to T-SQL DML Level 6: The Basics of Joining Tables and Record Sets
Gregory A. Larsen, MVP
December 14, 2011 at 7:06 am
First, nice article, great coverage of the topic. Couple things worth mentioning:
1 -
One reason why you might use the ordinal position in your ORDER BY clause is when the select list column you want to sort on contains a complicated expression, such as a CASE expression.
In that case, you should just alias the column, then use the alias in the ORDER BY rather than using ordinal position. Still allows you to change columns, saves having to rewrite the CASE
2 - your "numeric data sorting alphabetically" is misleading, that's not numeric data. Just because you're stuffing number characters into a string and calling it Number doesn't make it anything but character data. Character data should properly sort based on the value of the character, not the "intent" of the data. One more reason to store data in correct datatypes.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 14, 2011 at 7:18 am
Nice article, but I would have mentioned somewhere the COST of using the ORDER BY clause, perhaps demonstrating how SQL Server won't return any rows at all until the worktables have been built in order to enforce the ORDER BY clause.
When I demonstrate the cost of the ORDER BY clause to new developers, I often show a simple query first run w/o an ORDER BY and then adding an ORDER BY clause to demonstrate the difference in Management Studio. I think this is important for developers to understand. I've always told them to NEVER use an ORDER BY clause unless they needed server-side, ordered data becuase of the extra work SQL Server has to do in order to sort the data for you.
December 14, 2011 at 1:46 pm
You both have excellent points.
It is true the heading is a little misleading regarding sorting numeric data. I don't know how many times I've seen people putting numeric data in a character data type and then wonder why the ORDER BY clause reported that "10" came before "2".
I would hope people use a little common sense and do not use the ORDER BY clause if they don't need the data sorted. But then again it is always good to remind people that sorting data when you don't need it comes at a cost. Thank you for sharing.
Greg
Gregory A. Larsen, MVP
December 14, 2011 at 1:59 pm
Greg/Robert, just found a great example of the cost of order by here at work. I was wondering what you meant exactly, now I totally get it. Thanks!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 26, 2011 at 1:04 am
Nice Article Greg!
I think it would be good if you quote it as a bad practice for ‘Sorting Data based on Column Ordinal Position’. I understand you just wanted to demonstrate all the possible ways to sort the resultset but it’s a bad practice and we shouldn’t encourage it.
Overall nice work! Keep it up!!! 🙂
June 17, 2012 at 5:26 pm
Help.
Okay, so I'm reading through, and decided to try the case sensitive sorts:
"I’ll leave it up to you to run the SELECT statement in Listing 3 to show how the cars with a make of “Ford” will be sorted based on case."
And I refer back to Windows Collation Sorting Styles, and it indicates that "lowercase letters sort ahead of their uppercase versions." Great, so all lower case 'c's should appear before upper case 'C's. I added a couple extra entries to the same data, just so I could see it. The results were... not what I expected.
Make Model NumOfDoors ModelYear Color
-------------------------------------------------- -------------------------------------------------- ---------- --------- --------------------------------------------------
Chevrolet Volt 4 2010 Red
Chevrolet Camaro 4 1978 Green
Chevrolet Cruse 4 2012 Red
chevrolet Aveo 4 2011 White
Chevy Nova 3 1977 White
chevy Caprice 4 1984 Blue
Ford Focus 4 2012 Green
Ford Mustang 2 1964 Red
Ford Taurus 5 1995 White
ford F250 2 2010 Blue
ford F150 2 2010 Red
Honda Civic 4 2012 Red
Toyota Camry 4 2011 Red
Toyota Rav4 5 2000 Red
So, I expected all the lower case 'c's to be sorted together (as if lower case 'c' were a different letter, that came slightly before capital 'C' in the alphabet... it would appear that Upper Case letters sort first. (And why wouldn't these sort as 'chevrolet', 'chevy', 'Chevrolet', 'Chevy'?) The query run is Listing 3... with slight changes in the data.
Is the documentation wrong, or have I participated in too many Father's Day activities in the bright Maryland sun? (I assume it doesn't make any difference, but I'm using SQL 2008 R2 Dev Ed. (10.50.2500)
January 8, 2013 at 5:48 am
Often i will get an error stating, Columns used in ORder By was not listed in SELECT. Can anyone xplain when situtation arises..
--
Dineshbabu
Desire to learn new things..
January 8, 2013 at 5:50 am
Dineshbabu (1/8/2013)
Often i will get an error stating, Columns used in ORder By was not listed in SELECT. Can anyone xplain when situtation arises..
Sorry I got the answer in the error message itself..
On using DISTINCT Clause..
--
Dineshbabu
Desire to learn new things..
June 7, 2014 at 12:00 pm
Hi, there is a mistake in the 'Stairway to T-SQL DML Level 6: The Basics of Sorting Data Using the ORDER BY Clause' - I'm sure this is as a result of a re-edit since 8000 people have read the article already.
The code in Listing 2 has an
ORDER BY Car.Make ASC;
but underneath the results it states
Report 1: Output when ordering by Car.Make
Notice that I didn’t specify the ASC or DESC in my ORDER BY specification. By not specifying the sort direction, SQL Server will sort in ascending (ASC) order.
January 6, 2015 at 8:54 pm
Very helpful article, most appreciated.
Concerning the statement: - "One reason why you might use the ordinal position in your ORDER BY clause is when the select list column you want to sort on contains a complicated expression, such as a CASE expression". I would have liked further clarification or an example of such an expression as this has not been covered yet in the previous stairway steps.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply