SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to T-SQL DML Level 6: The Basics of Joining Tables and Record Sets


Stairway to T-SQL DML Level 6: The Basics of Joining Tables and Record Sets

Author
Message
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4307 Visits: 290
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
jcrawf02
jcrawf02
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Robert A. Shannon III
Robert A. Shannon III
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 31
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.
Greg Larsen
Greg Larsen
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4307 Visits: 290
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
jcrawf02
jcrawf02
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 19324
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Dev
Dev
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8802 Visits: 1602
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!!! :-)
Rich Weissler
Rich Weissler
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 954
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)
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 569
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..
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 569
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..
DouglasH
DouglasH
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 1469
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.




Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search