sql server mseq: Ordering Results -- sql server mseq

Ordering Results

Wes
8/24/2005 4:41:03 AM

sql server mseq:

Hi,

I have a result set that returns Loctation Codes.

Each location code is the following format: [Letter][2 Digit Number].

For example,
A10
B13
D04

What I would like to do is if the letter is either A, C, E, G then order the
results by the letter, then by the 2 digit numbers in ascending order.

If the letter is either B, D, F, H then order the results by the letter,
then by the 2 digit numbers in descending order.

So if A, C, E etc then numbers are ascending.
If B, D, F etc then numbers are descending.

A result set could look as follows
A01
A02
A03
A05
B30
B28
B27
B05
C03
C04
C07
D15
D12
D11

If I could use a case statement within the order by clause, it would solve
my problem, but I get an error when attempting to write a case statement
within the ORDER BY clause.

Thanks in advance,

Re: Ordering Results

Steve Kass
8/25/2005 1:14:45 AM

Wes,

Try this:

order by
substring(LocationCode,1,1),
case when substring(LocationCode,1,1) in ('A','C','E','G') then
substring(LocationCode,2,2) else null end,
case when substring(LocationCode,1,1) in ('B','D','F','H') then
substring(LocationCode,2,2) else null end DESC

You can't put the ASC and DESC keywords inside the case expression, but
the problem can still be solved.

Steve Kass
Drew University

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server mseq group.