September 30, 2013 at 6:59 pm
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
September 30, 2013 at 7:06 pm
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 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
September 30, 2013 at 8:51 pm
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.
September 30, 2013 at 10:53 pm
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.
September 30, 2013 at 10:57 pm
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 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
October 1, 2013 at 12:27 am
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
October 1, 2013 at 6:05 am
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.
October 1, 2013 at 7:40 am
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/
October 1, 2013 at 7:41 am
SQL_FS (9/30/2013)
SELECT string, categoryFROM @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/
October 1, 2013 at 9:35 am
Sean Lange (10/1/2013)
SQL_FS (9/30/2013)
SELECT string, categoryFROM @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
October 1, 2013 at 9:40 am
SQL_FS (10/1/2013)
Sean Lange (10/1/2013)
SQL_FS (9/30/2013)
SELECT string, categoryFROM @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/
October 1, 2013 at 12:06 pm
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
Change is inevitable... Change for the better is not.
October 1, 2013 at 12:42 pm
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.
October 1, 2013 at 5:49 pm
SQL_FS (10/1/2013)
Sean Lange (10/1/2013)
SQL_FS (9/30/2013)
SELECT string, categoryFROM @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 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
October 1, 2013 at 9:56 pm
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply