ORDER BY, Bad form?

  • So there's a trick I've used a few times with the ORDER BY clause in SQL, and I was just curious if anyone smarter than me (which is undoubtedly a good number of you) cared to chime in.

    Consider the following data set

    DECLARE @SampleData TABLE

    (

    ID int NOT NULL IDENTITY(1, 1)

    , string varchar(15) NOT NULL

    , category int NOT NULL

    );

    INSERT INTO @SampleData (string, category)

    VALUES ('Dragon', 9), ('Aardvark', 3), ('Bear', 6), ('Elephant', 14), ('llama', 11), ('Flounder', 12), ('Hippopotamus', 15), ('Ostrich', 23), ('Anteater', 35), ('Giraffe', 28), ('Carp', 7), ('Lion', 2)

    Now let's say that Dr. Frank comes along and asks for a list of the animals he's working with sorted alphabetically by name and including their category number. Simple enough right?

    SELECT string, category FROM @SampleData ORDER BY string ASC

    Well, something I learned when studying for my Querying SQL Server 2012 exam, which I passed a couple months back (hooray), was that there is an alternate acceptable syntax in the ORDER BY clause, so this query is functionally identical to the previous one.

    SELECT string, category FROM @SampleData ORDER BY 1 ASC

    Where the 1 indicates to order by the "first" column, which in this case is string. This is what brings me to my question relating to "bad form" with regard to the order by clause.

    It turns out Dr. Frank's category numbers aren't completely random, and animals that are part of the same experiment are connected by this value. Animals whose category is evenly divisible by 7 are part of experiment A, and animals whose category is evenly divisible by 3 are part of experiment B. Now Dr. Frank comes and asks for a list of all of his animals sorted by which experiment they are a part of (if applicable), then alphabetically by name, with Animals who are not part of experiment A or B lumped all together at the end of the report alphabetically.

    I would have always accomplished this in the past using a query like this

    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    But now I feel a bit dirty when I do it, because I feel like I am telling SQL server to sort by column 1, 2 or 3 based on my case statement rather than the numeric values themselves. Though tried and true testing has shown this not to be the case.

    So what do you guys think, bad form? Would the longer-format be better just for readability sake?

    SELECT

    string

    , category

    FROM

    (

    SELECT

    string

    , category

    , CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END AS [sorter]

    FROM @SampleData

    ) AS DataSet

    ORDER BY

    DataSet.sorter ASC

    , DataSet.string ASC

    --or this (added via Edit at 6:14 PM Pacific Standard Time)

    SELECT data.string, data.category

    FROM @SampleData data

    CROSS APPLY

    (

    SELECT CASE

    WHEN data.category % 7 = 0 THEN 1

    WHEN data.category % 3 = 0 THEN 2

    ELSE 3 END AS [sorter]

    ) AS sort

    ORDER BY

    sort.sorter ASC

    , string ASC

  • I never use the column positional (I think it is called ordinal) as the SORT BY column.

    I think it is much more clear to use the explicit column name, so that there is never any lack of clarity when you need to use a formula to compute the ordering column.

    It may be possible that I've heard somewhere that the ordinal ORDER BY syntax may be deprecated at some time but I could be wrong about that.

    You should have made this a Poll question.

    My vote: Bad form


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • SELECTsd.ID,

    sd.string,

    sd.category,

    ce.experiment

    FROM @SampleData sd

    INNER JOIN (

    SELECT category,

    CASEWHEN category % 7 = 0 THEN 'A'

    WHEN category % 3 = 0 THEN 'B'

    ELSE 'C'

    END AS experiment

    FROM @SampleData

    ) ce

    ON sd.category = ce.category

    ORDER BY ce.experiment, sd.string

    Following on from Dwain's comments, another issue is non-atomic data, category is not just a primary key, it also denotes the experiment. If you have a CategoryExperiment relation, then it's easy street.

  • If ordinal position of column is used instead of column name, then a new programmer will always have to look into the table to find out which column is used for sorting.

    You add a column somewhere in the start or middle or your table and all ordinals will be changed. Now you have to correct all of your queries.

  • T.Ashish (9/30/2013)


    You add a column somewhere in the start or middle or your table and all ordinals will be changed. Now you have to correct all of your queries.

    I believe you mean:

    You add a column somewhere in the start or middle or your return results and all ordinals may be changed. Now you have to correct all of your queries this query.

    Adding columns to the table will have no impact unless you're doing something else that is bad form, which is SELECT *.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I think people are misunderstanding, allow me to clarify. I'm not trying to ask whether or not using ORDER BY [Column Position] is bad form or not. I merely pointed out the "feature" of using ORDER BY [Column Position] to illustrate how reading that code is similar to reading the code of my CASE statement in the ORDER BY clause. My question is relating to people's opinion of using the CASE statement in the where clause versus the subquery/cross apply solution.

  • The elephant in the room is the failure to meet first normal form. Being fancy with ordinals or order by case statements is beside the point.

  • T.Ashish (9/30/2013)


    If ordinal position of column is used instead of column name, then a new programmer will always have to look into the table to find out which column is used for sorting.

    You add a column somewhere in the start or middle or your table and all ordinals will be changed. Now you have to correct all of your queries.

    In addition to Dwain's corrections, the actual columns in the table are not the most common change. Let's say somebody wants to add a new column to the query but they want this new column as the first column. This required no changes to the tables but the ordinal position of the original columns has changed.

    _______________________________________________________________

    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/

  • SQL_FS (9/30/2013)


    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    This is NOT ordering by ordinal position. This is ordering by a derived column in the where clause. You are in affect ordering by the hardcoded values, not the column position.

    _______________________________________________________________

    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/

  • Sean Lange (10/1/2013)


    SQL_FS (9/30/2013)


    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    This is NOT ordering by ordinal position. This is ordering by a derived column in the where clause. You are in affect ordering by the hardcoded values, not the column position.

    I understand that as I indicated in my original post I feel dirty because it now looks very similar to me to ordering by ordinal position. I'm just asking if others feel that it is bad form to do this in the order by clause.

    Mods, please feel free to close this thread I've somehow managed to derail my own thread and can't get it back on track!. 🙂

  • SQL_FS (10/1/2013)


    Sean Lange (10/1/2013)


    SQL_FS (9/30/2013)


    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    This is NOT ordering by ordinal position. This is ordering by a derived column in the where clause. You are in affect ordering by the hardcoded values, not the column position.

    I understand that as I indicated in my original post I feel dirty because it now looks very similar to me to ordering by ordinal position. I'm just asking if others feel that it is bad form to do this in the order by clause.

    Mods, please feel free to close this thread I've somehow managed to derail my own thread and can't get it back on track!. 🙂

    Threads don't close around here. 😉

    However, I don't think what you are doing there is bad form. I understand your concern about what it might look like. If the concern is great, add some comments to explain it.

    _______________________________________________________________

    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/

  • IIRC, ordinal ORDER BYs have been deprecated. Even if they weren't, I see no advantage to using them other than typing fewer characters.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I'll try to get the thread back on track and answer your actual question...

    My experience is a proc gets written once and then goes through dozens of enhancements for the lifetime of a project. Therefore, one should always put in extra effort the very first time it is written so that it is extremely easy for others to understand and enhance later. Paying the price up-front in writing a derived table will make the rest of the query easier to understand and write.

    I really like your longer script because it makes explicit the over-multiplexed information hidden in a single column. And I really like deriving tables in the FROM CLAUSE so that it normalizes poorly designed tables. Derived tables can also greatly encapsulate filtering and logic in a single place, allowing one to reuse sub-queries.

  • SQL_FS (10/1/2013)


    Sean Lange (10/1/2013)


    SQL_FS (9/30/2013)


    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    This is NOT ordering by ordinal position. This is ordering by a derived column in the where clause. You are in affect ordering by the hardcoded values, not the column position.

    I understand that as I indicated in my original post I feel dirty because it now looks very similar to me to ordering by ordinal position. I'm just asking if others feel that it is bad form to do this in the order by clause.

    Mods, please feel free to close this thread I've somehow managed to derail my own thread and can't get it back on track!. 🙂

    I feel some responsibility for deviating your thread from track so I too will help bring it back.

    If you're concerned about miscommunicating the intent, you could do something like this:

    SELECT string, category

    FROM @SampleData

    CROSS APPLY

    (

    SELECT CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3 END

    ) a (orderby)

    ORDER BY orderby ASC, string ASC

    That makes your intention pretty clear.

    I don't often use a derived column in an ORDER BY, although I have suggested a few in forum threads. I don't see a particular problem with them, mainly because I so rarely see ORDER BY ordinal constructs.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • SQL_FS (10/1/2013)


    I think people are misunderstanding, allow me to clarify. I'm not trying to ask whether or not using ORDER BY [Column Position] is bad form or not. I merely pointed out the "feature" of using ORDER BY [Column Position] to illustrate how reading that code is similar to reading the code of my CASE statement in the ORDER BY clause. My question is relating to people's opinion of using the CASE statement in the where clause versus the subquery/cross apply solution.

    Understood. But, because you wrote the following in your original post...

    Well, something I learned when studying for my Querying SQL Server 2012 exam, which I passed a couple months back (hooray), was that there is an alternate [font="Arial Black"]acceptable syntax in the ORDER BY clause[/font], so this query is functionally identical to the previous one.

    SELECT string, category FROM @SampleData ORDER BY 1 ASC

    ... I thought Id stress that ordinal ORDER BYs do not constitute "acceptable syntax" other than the fact that it unfortunately works.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

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