Consider the following table named Persons:
Key Name Type Date Pack
1 Pocoyo KIND 30-11-2011 1
2 Erik MAN 10-10-1980 1
3 Alinda VROUW 12-12-1991 1
4 Pingu KIND 11-12-2012 1
5 Elisia KIND 11-11-2010 1
6 Kees MAN 10-11-1984 2
Now I would like to sort this table on Pack, Type and Date, but I would like the Type to be sorted like MAN, VROUW, KIND, so basically the desired outcome should be like:
Key Name Type Date Pack
2 Erik MAN 10-10-1980 1
3 Alinda VROUW 12-12-1991 1
5 Elisia KIND 11-11-2010 1
1 Pocoyo KIND 30-11-2011 1
4 Pingu KIND 11-12-2012 1
6 Kees MAN 10-11-1984 2
How can I create this query?
Try out
SELECT *
FROM Persons
ORDER BY
Pack,
CASE Type
WHEN 'MAN' THEN 1
WHEN 'VROUW' THEN 2
WHEN 'KIND' THEN 3
END,
Date ASC
MSDN: CASE (Transact-SQL)
CASE can be used in any statement or clause that allows a valid
expression. For example, you can use CASE in statements such as
SELECT, UPDATE, DELETE and SET, and in clauses such as select_list,
IN, WHERE, ORDER BY, and HAVING.
I would be inclined to create a new reference data table, say PersonType to hold the expected values of the "Type" column and their relative sort priority:
Type SortPriority
MAN 1
VROUW 2
KIND 3
And do a left outer join onto this table to determine the sort order for the results of the query against Persons:
SELECT
*
FROM
Persons p
LEFT OUTER JOIN PersonType pt on p.Type = pt.Type
ORDER BY
Pack,
SortPriority,
Date
I assume, because of your comments, your non-standard order is due to the order of the items in the enum. If that's the case, consider storing the value, not the name of the enum in the database. Let your DAL convert the enum when reading/writing to the DB. For example, today in EntityFramework we can write a small wrapper class that becomes the type of the enum column and define equivalence and implicit conversions between the wrapper and the enum. See this answer for more information on enums and EF. In the June 2011 CTP EF adds support for this translation directly.
