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 Tuesday, October 1, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 13,081, Visits: 12,545
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 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 #1500475
Posted Tuesday, October 1, 2013 12:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500536
Posted Tuesday, October 1, 2013 12:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 142, Visits: 923
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.
Post #1500553
Posted Tuesday, October 1, 2013 5:49 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: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!

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 #1500629
Posted Tuesday, October 1, 2013 9:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
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 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



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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500648
Posted Tuesday, October 1, 2013 10:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
I have a bit of a problem doing it the way you did it.

First, the ORDER BY you created is what I call a "Blind Sort". There is nothing in the output to give the reader any clue as to what the sort order actually is.

Second, you've eliminated some important information. If you're going to take the time to do the calculation to unravel the poor design of multiplexing the experiment number with the category (violates first normal form and either requires esoteric knowledge or documentation external to the database), why not also derive the Experiment name (either "A", "B", or an implied "?" according to your original post) and display it? Display of that data will also solve the first problem of the output being the result of a "Blind Sort".

Last but not least, the code has become unnecessarily complicated and that also because of the "Blind Sort". Displaying the derived Experiment Name will allow you to greatly simplify the code and make your users say nice things about you because you removed the "Blind Sort" and the requirement for esoteric knowledge.

Jason L. had absolutely the right idea and started his code this way and actually did remove the "Blind Sort" but also got caught in the trap of using a derived table to resolve the experiment name, which also requires two visits to the table instead of just 1.

The key to this whole thing is the understanding that ORDER BY can and does work on a derived column name.

With all of that in mind, here's how I'd simplify the code...

--====================================================
-- Create the sample table and data
--====================================================
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)
;
--====================================================
-- Create the ordered output without a "Blind
-- Sort" and make the esoteric multiplex of two
-- pieces of data per value much more obvious.
-- Doing all of that will make the users very
-- happy and make the code much less complex.
--====================================================
SELECT Experiment = CASE
WHEN Category%7 = 0 THEN 'A'
WHEN Category%3 = 0 THEN 'B'
ELSE '?'
END
,String
,Category
FROM @SampleData
ORDER BY Experiment, String
;


Here are the results...

Experiment String          Category
---------- --------------- -----------
? Lion 2
? llama 11
? Ostrich 23
A Anteater 35
A Carp 7
A Elephant 14
A Giraffe 28
B Aardvark 3
B Bear 6
B Dragon 9
B Flounder 12
B Hippopotamus 15



The reasons why users will love it this way is because they can drop it into a spreadsheet (we all know spreadsheets make the world go 'round) and then they can easily apply column filters to it to easily display just the experiment(s) they want to see.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500651
Posted Tuesday, October 1, 2013 11:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
Shifting gears a bit and owing to the fact that sometimes users actually do want blind sorts, the following will work, still only hits the table once, is very easy to modify to display the Experiment Name when the users figure out that they really do need to see it, employees highly desirable TOP DOWN programing, and is still very simple. Even a "fresher" could troubleshoot or modify the code.

--====================================================
-- Create the sample table and data
--====================================================
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)
;
--====================================================
-- Do the "Blind Sort" but do it in a TOP DOWN
-- fashion, which is also an easy thing to change
-- when the users complain about the "Blind Sort"
-- and the missing Experiment Name.
--====================================================
WITH
cteEnumerateTheSort AS
(
SELECT Experiment = CASE
WHEN Category%7 = 0 THEN 1
WHEN Category%3 = 0 THEN 2
ELSE 3
END
,String
,Category
FROM @SampleData
)
SELECT String,Category
FROM cteEnumerateTheSort
ORDER BY Experiment,String
;


Here are the results...

String          Category
--------------- -----------
Anteater 35
Carp 7
Elephant 14
Giraffe 28
Aardvark 3
Bear 6
Dragon 9
Flounder 12
Hippopotamus 15
Lion 2
llama 11
Ostrich 23



Of course, it looks like those last 3 items could be part of the second experiment, which is the big problem with "Blind Sorts".


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500653
Posted Wednesday, October 2, 2013 12:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 35,366, Visits: 31,901
BAAA-HAAAA!!!! What the heck... here's what I call the "Half Blind and Crazy" method. You see why it's "Half Blind" and it's "Crazy" because you really shouldn't do this type of stuff anywhere except in the frontend.

--====================================================
-- Create the sample table and data
--====================================================
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)
;
--====================================================
-- "Half Blind and Crazy"
--====================================================
WITH
cteEnumerateTheSort AS
(
SELECT Experiment = CASE
WHEN Category%7 = 0 THEN 'A'
WHEN Category%3 = 0 THEN 'B'
ELSE '?'
END
,String = LTRIM(String COLLATE LATIN1_GENERAL_BIN)
,Category = RIGHT(' ' + LTRIM(Category),5)
FROM @SampleData
UNION ALL SELECT 'A',CHAR(160),''
UNION ALL SELECT 'B',CHAR(160),''
UNION ALL SELECT '?',CHAR(160),''
),
cteRowType AS
(
SELECT RowType = ROW_NUMBER() OVER (PARTITION BY Experiment ORDER BY String)
,Experiment,String,Category
FROM cteEnumerateTheSort
)
SELECT Experiment = CASE WHEN RowType = 1 THEN ' '+Experiment ELSE '' END
,String
,Category
FROM cteRowType rt
ORDER BY rt.Experiment, rt.String
;


Here are the crazy results...

Experiment String          Category
---------- --------------- --------
? Lion 2
Ostrich 23
llama 11
 
A Anteater 35
Carp 7
Elephant 14
Giraffe 28
 
B Aardvark 3
Bear 6
Dragon 9
Flounder 12
Hippopotamus 15



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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500657
Posted Wednesday, October 2, 2013 12:23 AM


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: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
Jeff - Methinks you had way, way too much time on your hands this evening!

Seriously, I think you've added some truly thoughtful and insightful suggestions to this thread.

If nothing else, I'll always remember the "blind sort."



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 #1500660
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse