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

  • Greg Larsen

    SSC-Insane

    Points: 20605

    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

    SSC-Insane

    Points: 24198

    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."

  • Robert A. Shannon III

    SSC Veteran

    Points: 234

    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

    SSC-Insane

    Points: 20605

    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

    SSC-Insane

    Points: 24198

    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."

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42488

    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

    Hall of Fame

    Points: 3235

    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

    Hall of Fame

    Points: 3220

    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

    Hall of Fame

    Points: 3220

    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

    SSCommitted

    Points: 1669

    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.

  • anthony.rooney

    Old Hand

    Points: 382

    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 11 (of 11 total)

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