How to apply non standard SQL column sort order?

Question

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?

Noop, the `type` column is a `varchar` which is controlled by the `ToString` of a .Net `enum`. — Kees C. Bakker, Nov 26 '11 at 14:12

sll · Accepted Answer · 2011-11-26T14:23:44.997

8

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.

edited Nov 26 '11 at 14:23

answered Nov 26 '11 at 14:13

sll

61,540
22
104
156

Hm... I seem to loose the ordering by `Pack` and `Date`. – Kees C. Bakker Nov 26 '11 at 14:14
1

I assumed that most tricky would be sort by TYPE columnd and you would be able adding other columns yourself, sorry I will add this as well – sll Nov 26 '11 at 14:16
Ah, I never knew you could add a `CASE` to an `ORDER`, nice! :D Thanks – Kees C. Bakker Nov 26 '11 at 14:20
@Kees C. Bakker : yes this is the nice feature! I've added extract from the MSDN documentation regarding this point to my answer as well – sll Nov 26 '11 at 14:31
@ssl, maybe you have some ideas about this one as well: http://stackoverflow.com/questions/8279571/how-to-sort-with-a-case-statement-in-an-entitydatasource – Kees C. Bakker Nov 26 '11 at 16:02
@Kees C. Bakker : unfortunately I'm not aware with EF :( – sll Nov 27 '11 at 16:41

Ian Nelson · Answer 2 · 2011-11-26T21:08:36.877

3

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

edited Nov 26 '11 at 21:08

answered Nov 26 '11 at 14:19

Ian Nelson

57,123
20
76
103

It works, but I get a more complex data model. Not sure what's the best way to go here. What if I would like to sort on `VROUW`, `MAN`, `KIND` in the future? – Kees C. Bakker Nov 26 '11 at 14:22
1

Then you would update the values in the SortPriority field in this reference data table. I would prefer modifying reference data to achieve this change compared to modifying a SQL query (or several queries, if this sort order is used elsewhere), as that could potentially require a recompile / redeployment. – Ian Nelson Nov 26 '11 at 21:06

score 2 · Answer 3 · edited May 23 '17 at 11:55

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.

How to apply non standard SQL column sort order?

3 Answers3

Linked