134
Part II: Building Databases and Working with Data
FROM Production.ProductDescription
WHERE
Description LIKE 'Replacement%'
ORDER BY
CASE
WHEN LEFT(Description, 5) = 'This '
THEN Stuff(Description, 1, 5, '')
ELSE
Description
END;Result:Description TextLength
--------------------------------- ----------
Replacement mountain wheel for entry-level rider. 49
Replacement mountain wheel for entry-level rider. 49
Replacement mountain wheel for the casual to serious rider. 59
Replacement mountain wheel for the casual to serious rider. 59
Replacement rear mountain wheel for entry-level rider. 54
Replacement rear mountain wheel for the casual to serious rider. 64
Replacement rear wheel for entry-level cyclist. 47
Replacement road front wheel for entry-level cyclist. 53
Replacement road rear wheel for entry-level cyclist. 52...
Specifying the ORDER BY Using Column Aliases
Alternatively, you can use a column alias to specify the columns used in the ORDER BY
clause. This is the preferred method for sorting by an expression because it makes the code
easier to read. This example sorts in descending order, rather than the default ascending
order:USE AdventureWorks
SELECT LastName + ', ' + FirstName as FullName
FROM Person.Person
ORDER BY FullName DESC;Result:FullName
-------------
Zwilling, Michael
Zwilling, Michael
Zukowski, Jake
Zugelder, Judy
Zubaty, Patricia
Zubaty, Carla
Zimprich, Karin...
c06.indd 134c06.indd 134 7/30/2012 4:16:06 PM7/30/2012 4:16:06 PM
http://www.it-ebooks.info