October 1, 2013 at 10:51 pm
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:-D) and then they can easily apply column filters to it to easily display just the experiment(s) they want to see.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2013 at 11:09 pm
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
Change is inevitable... Change for the better is not.
October 2, 2013 at 12:11 am
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. :hehe:
--====================================================
-- 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
Change is inevitable... Change for the better is not.
October 2, 2013 at 12:23 am
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 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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply