Adolfo Perez Adolfo Perez - 5 days ago 6
SQL Question

Conditional multi-column database sorting

I have the following GlobalTax table below and I need to sort by TaxCode but with a particular sort order. The first set of items should be the ones which first character of Type column = 'F', then 'S' and finally 'L'

enter image description here

I'm able to sort by type like this:

select TaxCode,
Type,
CASE WHEN LEFT(gt.Type, 1) = 'F' THEN 1
WHEN LEFT(gt.Type, 1) = 'S' THEN 2
WHEN LEFT(gt.Type, 1) = 'L' THEN 3
ELSE 4
END as SortOrder
from GlobalTax gt
order by sortorder


Which returns:

enter image description here

However, it's not sorting each set Alphabetically by TaxCode.
What am I missing?

Here is a fiddle: http://rextester.com/JGLNP57037

Answer

Simply add taxcode to order by:

select TaxCode,
   Type,
    CASE WHEN LEFT(gt.Type, 1) = 'F' THEN 1
         WHEN LEFT(gt.Type, 1) = 'S' THEN 2
         WHEN LEFT(gt.Type, 1) = 'L' THEN 3
    ELSE 4
    END as SortOrder
from GlobalTax gt
order by sortorder, taxcode

You can also simlify the case expression:

select TaxCode,
   Type,
    CASE LEFT(gt.Type, 1)
         WHEN 'F' THEN 1
         WHEN 'S' THEN 2
         WHEN 'L' THEN 3
    ELSE 4
    END as SortOrder
from GlobalTax gt
order by sortorder, taxcode
Comments