Case statement in Order by clause

Hi

I need a case statement with in the order clause,

There are fields like below

Feild A FieldB LevelA LevelB

xxxx xxxx

xxxx xxxx

xxxx xxxx 3 2

xxxxx xxxx 2 1

xxxxxx xxxxx 1 1

I need to sort LevelA and LevelB first leaving out the null or blank values in the top rows

Feild A FieldB LevelA LevelB

xxxx xxxx 1 1

xxxx xxxx 2 1

xxxx xxxx 3 2

xxxxx xxxx

xxxxxx xxxxx

Thanks

like this:

Code Snippet

create table #t (FieldA varchar(10), FieldB varchar(10), LevelA int, LevelB int)

insert into #t

select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', null, null

union all select 'xxxx', 'xxxx', 3, 2

union all select 'xxxxx', 'xxxx', 2, 1

union all select 'xxxxxx', 'xxxxx', 1, 1

union all select 'xxxxxx', 'xxxxx', 1, 2

union all select 'xxxxxx', 'xxxxx', 1, 3

select *

from #t

order by case when LevelA is not null then 1 else 9 end,

LevelA,

case when LevelB is not null then 1 else 9 end,

LevelB

|||

You can use a CASE expression like below:

order by coalesce(LevelA, power(2., 31)-1), coalesce(LevelB, power(2., 31)-1)

|||Umachandar,

Some readers might why you said "CASE expression," so I'll remark that COALESCE(a,b) is basically the same as CASE WHEN a IS NULL THEN b ELSE a END.

Sowree,

I think the suggestion below will work, and here I've added the requirement that blank values appear last. (If or are number types, they can't be blank, and this code will incorrectly put values of 0 last, because '' is implicitly converted to 0 in the comparison.)

ORDER BY
CASE WHEN a IS NULL OR a = '' THEN 1 ELSE 0 END,
a,
CASE WHEN b IS NULL OR b = '' THEN 1 ELSE 0 END,
b

Your question isn't entirely clear, so you may have to adapt the suggestions to your exact need.

Steve Kass
Drew University
http://www.stevekass.com