RE: conditional 'order by'
Date: Fri, 19 Dec 2008 12:46:20 -0500
Message-ID: <954AD0DE85304CE990F9FE1F40E65CC3@KenPC>
Just use a case statement.
Order by case when substr(code,2,1)='F' then decode(code,'F',0,'H',1,2)
When substr(code,2,1)='H' thendecode(code,'H',0,'F',1,2)
Else .
End
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Eugene Pipko
Sent: Friday, December 19, 2008 11:57 AM
To: 'oracle-l_at_freelists.org'
Subject: conditional 'order by'
Hi all,
I have a query that needs to be ordered by based on the second letter of the code passed in it.
For instance if the code is '9F9Q' then I'd order by 'F' , then by 'H', then by any other.
If the code is '7H7Q' then I'd order by 'H', then by 'F', then by any other.
I know that I can do it in 2 steps, but is it possible to do in one sql statement?
Select .
From .
Where ..
If substr(code,2,1)='F' then
Order by decode(code,'F',0,'H',1,2);
Elsif substr(code,2,1)='H' then
Order by decode(code,'H',0,'F',1,2);
End if;
Thanks,
Eugene
P Please consider the environment before printing this e-mail.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 19 2008 - 11:46:20 CST