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.