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

CASE function result with result expression values (for IN keyword)

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE
GROUP.GROUP_ID = 2
AND DEPT.DEPT_ID = 'D'
AND WORK_TYPE_ID IN
(
CASE DEPT_ID
WHEN 'D' THEN 'A','B','C' <- ERROR
WHEN 'F' THEN 'C','D
ELSE 'A','B','C','D'
END
)

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44
Incorrect syntax near the keyword 'WHERE'.

which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

AND

(

(CASE DEPT_ID = 'D' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID = 'F' AND WORK_TYPE_ID IN ('A','B','C'))

OR
(CASE DEPT_ID != 'D' AND CASE DEPT_ID != 'F' AND

WORK_TYPE_ID IN ('A','B','C'))

)

Though this could lead to bad performance :-(

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks for the reply . It works, and I agree with you that it could lead to performance degradation.

However, if the number of records involved are filtered and limited to, say under 1000 rows, it would still be managable ? Just a feeling, I know it is hard to quantify the expense of a query by just the row count alone.

Kenny