How to order by alphanumeric codes in SQL Server I have a table with some project codes. These codes are 

e.g.: B001, B002, Z15001, Z14001, P003,...


I need this result table:

Z15001
Z15002
...
B001
B002
...
C001
C002
...
Z14099
Z14098
Z14097
...
Z13099
Z13098
...
Z13001


I tried this but the order is wrong.

select *
from table
order by
    case
        when Kod like 'Z15%' then 1
        when Kod like 'B%' then 2
        when Kod like 'C%' then 3
        when Kod like 'D%' then 4
        when Kod like 'E%' then 5 else 6 end asc, Kod asc
    , case when Kod NOT LIKE 'B%' 
        AND Kod NOT LIKE 'C%' 
        AND Kod NOT LIKE 'D%' 
        AND Kod NOT LIKE 'E%' 
        AND Kod NOT LIKE 'Z15%' then 6 
      end desc


How I can do this in SQL Server?
In case you are interested on running this kind of sort repeatedly, or on a larger dataset, you may want to consider adding a key table to control the sort order, and JOINing to that table.

First, we setup the test environment:

USE tempdb;
CREATE TABLE dbo.K
(   KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K 
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.KSort
(
    KODSort INT NOT NULL CONSTRAINT PK_KSort
        PRIMARY KEY CLUSTERED
    , KODPrefix VARCHAR(255) NOT NULL 
);


INSERT INTO dbo.K (KOD)
VALUES ('Z15001')
    , ('Z15002')
    , ('B001'  )
    , ('B002'  )
    , ('C001'  )
    , ('C002'  )
    , ('Z14099')
    , ('Z14098')
    , ('Z14097')
    , ('Z13099')
    , ('Z13098')
    , ('Z13001');

INSERT INTO dbo.KSort(KODSort, KODPrefix)
VALUES (10, 'Z15%')
    , (20, 'B%')
    , (30, 'C%')
    , (40, 'Z14%')
    , (50, 'Z13%');

SELECT K.*
FROM dbo.K;


This shows the expected result, which just happens to be sorted in order of the clustering key:



Now, two queries, the first with the standard ORDER BY clause to sort the results "by hand".

SELECT *
FROM dbo.K
ORDER BY
    CASE
        WHEN Kod LIKE 'Z15%' THEN 1
        WHEN Kod LIKE 'B%' THEN 2
        WHEN Kod LIKE 'C%' THEN 3
        WHEN Kod LIKE 'Z14%' THEN 4
        WHEN Kod LIKE 'Z13%' THEN 5 
        ELSE 6 
    END ASC;


The plan for this is:



Nice and simple.  The "Sort" operator is taking 77.6% of the plan cost.

If we use a JOIN to accomplish our sort instead, like so:

SELECT K.*
FROM dbo.K
    INNER JOIN dbo.KSort ON K.KOD LIKE KSort.KODPrefix
ORDER BY KSort.KODSort, K.KOD;


We start with a much simpler piece of code, which appeals to me on a deep level; however we also see an interesting execution plan:



Instead of a sort operator, we now have several nested loop joins.

When dealing with a small amount of data, the difference is inconsequential once we have the data in SQL Server's buffer.

However, lets see what happens when we add more rows to the KOD table.

TRUNCATE TABLE dbo.K;

INSERT INTO dbo.K(KOD)
SELECT TOP(1000000) CASE ROW_NUMBER() OVER (ORDER BY o.object_id) % 5
   WHEN 0 THEN 'Z15' 
   WHEN 1 THEN 'B' 
   WHEN 2 THEN 'C' 
   WHEN 3 THEN 'Z14' 
   WHEN 4 THEN 'Z13' END
    + CONVERT(VARCHAR(255), ROW_NUMBER() OVER (ORDER BY o.object_id))
FROM sys.objects o, sys.objects o1, sys.columns c;


(The above adds 1,000,000 rows to the table)

On my machine, which is a 4-core Intel I7 with 16GB RAM, I see the following with SET STATISTICS IO,TIME ON; configured:

The "standard" sort:

(1000000 row(s) affected)
Table 'K'. Scan count 9, logical reads 2679, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 6463 ms.


The "JOIN":

(1000000 row(s) affected)
Table 'K'. Scan count 5, logical reads 2641, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KSort'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 5475 ms.


I used DBCC FREEPROCCACHE before starting each query, the results are very similar across multiple runs.  Typically the standard sort is 2 to 3 times slower than the JOIN version.

Above-and-beyond performance implications, using the key-table I propose (dbo.KSort) allows you to change the sort order of the output at will without having to ever modify code, which is a pretty nice bonus, in my opinion.  You simply modify the values of the KODSort column so the results are returned in whatever order you like.  For instance, the following UPDATE would make the Z14* rows display prior to the B* rows:

UPDATE KSort SET KODSort = 15 WHERE KODSort = 40;