Conditional multi-column database sorting

Question

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'

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:

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

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

jarlh · Accepted Answer · 2016-12-01 14:46:57Z

2

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

answered Dec 1, 2016 at 14:46

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional multi-column database sorting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related