Adolfo Perez - 10 months ago 54

SQL 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

Answer Source

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
```