SQL: Order By Substitution
Author: jason
Date: 2014-01-23 12:28:50
Category: Musing

Sometimes we need the ability to "arbitrarily" order rows out of a table. A CASE statement can be used after ORDER BY to let us decide how the results will be sorted and returned.

Apples are in the middle, because of course Oranges come first.





Code

DECLARE @Temp AS TABLE ([id] BIGINT, [name] VARCHAR(10), [number] INT)
INSERT INTO @Temp SELECT 1, 'Apple', 10
INSERT INTO @Temp SELECT 2, 'Peach', 1
INSERT INTO @Temp SELECT 3, 'Apple', 7
INSERT INTO @Temp SELECT 4, 'Peach', NULL
INSERT INTO @Temp SELECT 4, 'Peach', NULL
INSERT INTO @Temp SELECT 5, 'Orange', NULL

SELECT * FROM @Temp t
ORDER BY
(CASE WHEN t.name = 'Apple' THEN 2
WHEN t.name = 'Peach' THEN 3
WHEN t.name = 'Orange' THEN 1 ELSE 0 END) ASC





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009