Re: Ordering Results

From: Steve Kass <skass@xxxxxxxx>
Date: Thu, 25 Aug 2005 01:14:45 -0400

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


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.

References:
- Ordering Results
  - From: Wes

Prev by Date: Ordering Results
Next by Date: How to Join a table with Other (result) Tables ? Complex one !
Previous by thread: Ordering Results
Next by thread: How to Join a table with Other (result) Tables ? Complex one !
Index(es):
- Date
- Thread

Relevant Pages

Ordering Results
... then by the 2 digit numbers in ascending order. ... then by the 2 digit numbers in descending order. ... If I could use a case statement within the order by clause, ...
(microsoft.public.sqlserver.mseq)
Re: lots of data to be sorted for each unique record
... Make sure you sort your data in ascending order by name and descending order by ... john smith 78% ...
(microsoft.public.excel.misc)
Re: need to do a string replace of "asc" to "desc" or "desc" to "asc" first occurrence only
... You have an error in your SQL syntax; ... I'll keep looking at it but apparently it chops the ORDER BY clause ... I have a SQL "ORDER BY" clause that will be configured like $orderBy 's ... // it was in descending order ...
(comp.lang.php)
RE: VLOOKUP Keeps Returning #N/A
... approx. ... 42k numbers/cells. ... Column B has the 17 digit numbers as well but no '. ... I did sort Column B in ascending order. ...
(microsoft.public.excel.worksheet.functions)
Re: cumulative field
... There will be a problem with the SQLECT query if there are two identical ... as the GROUP BY clause will re-order them. ... If your table has a unique key column in ascending order it can by used to ... > How can i perform this by foxpro program, ...
(microsoft.public.fox.programmer.exchange)