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 12»»

ORDER BY, Bad form? Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 6:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 10:19 AM
Points: 28, Visits: 197
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

Post #1500241
Posted Monday, September 30, 2013 7:06 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 3,617, Visits: 5,237
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500243
Posted Monday, September 30, 2013 8:51 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, June 1, 2014 8:05 PM
Points: 535, Visits: 430
SELECT	sd.ID,
sd.string,
sd.category,
ce.experiment
FROM @SampleData sd
INNER JOIN (
SELECT category,
CASE WHEN 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.
Post #1500254
Posted Monday, September 30, 2013 10:53 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 2:58 AM
Points: 718, Visits: 543
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.
Post #1500260
Posted Monday, September 30, 2013 10:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:45 PM
Points: 3,617, Visits: 5,237
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500261
Posted Tuesday, October 1, 2013 12:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 10:19 AM
Points: 28, Visits: 197
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.
Post #1500268
Posted Tuesday, October 1, 2013 6:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, June 1, 2014 8:05 PM
Points: 535, Visits: 430
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.
Post #1500358
Posted Tuesday, October 1, 2013 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1500412
Posted Tuesday, October 1, 2013 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 13,138, Visits: 11,977
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1500414
Posted Tuesday, October 1, 2013 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, January 17, 2014 10:19 AM
Points: 28, Visits: 197
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!. :)
Post #1500472
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse