|
Home > Archive > MS SQL Server MSEQ > August 2005 > Ordering Results
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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,
Wes.
| |
| Steve Kass 2005-08-25, 3:36 am |
| Wes,
Try this:
order by
substring(LocationCo
de,1,1),
case when substring(LocationCo
de,1,1) in ('A','C','E','G') then
substring(LocationCo
de,2,2) else null end,
case when substring(LocationCo
de,1,1) in ('B','D','F','H') then
substring(LocationCo
de,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
Wes wrote:
>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,
>Wes.
>
>
|
|
|
|
|