Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to T-SQL DML Level 6: The Basics of Joining Tables and Record Sets Expand / Collapse
Author
Message
Posted Monday, November 14, 2011 4:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1205019
Posted Wednesday, December 14, 2011 7:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
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."
Post #1221641
Posted Wednesday, December 14, 2011 7:18 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 3, 2014 11:04 AM
Points: 68, Visits: 30
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.
Post #1221654
Posted Wednesday, December 14, 2011 1:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1222020
Posted Wednesday, December 14, 2011 1:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
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."
Post #1222032
Posted Monday, December 26, 2011 1:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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!!!


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1226624
Posted Sunday, June 17, 2012 5:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 1, 2014 9:55 AM
Points: 1,823, Visits: 905
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)
Post #1317098
Posted Tuesday, January 8, 2013 5:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:08 AM
Points: 987, Visits: 567
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..
Post #1404189
Posted Tuesday, January 8, 2013 5:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:08 AM
Points: 987, Visits: 567
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..
Post #1404190
Posted Saturday, June 7, 2014 12:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 63, Visits: 365
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.



Post #1578590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse