ORDER BY, Bad form?

Question

ORDER BY, Bad form?

CodeMuddler

SSC Eights!

Points: 925
More actions
September 30, 2013 at 6:59 pm

#283335

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
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

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

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

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

Jason L SSC Eights! Points: 849 More actions · Answer 2

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.

T.Ashish SSCarpal Tunnel Points: 4248 More actions · Answer 3

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

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

CodeMuddler SSC Eights! Points: 925 More actions · Answer 5

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.

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

Jason L SSC Eights! Points: 849 More actions · Answer 6

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.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

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/

CodeMuddler SSC Eights! Points: 925 More actions · Answer 9

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!. 🙂

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

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/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

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)

Bill Talada SSChampion Points: 11954 More actions · Answer 12

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.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

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)