4 Replies Latest reply on Nov 20, 2012 12:41 PM by user564819

decode for order by combined with ass and desc

Hi All,
I've a query which's results needs to be ordered dynamically. The issue is that I need to order by some values in desc and some others in desc:
``````select cust.id, cust.name, cust.region
from   customer cust
order by cust.id asc, cust.name desc, cust.region asc``````
What I need now that if cust.sort_value = 1 then i need to order by cust.id asc, cust.name desc, cust.region asc
and if the cust.sort_value = 2 need to order by cust.region asc

Since cust.name is non numeric value I can't do
``````select cust.id, cust.name, cust.region
from   customer cust
order by case when cust.sort_value = 1 then cust.id || cust.name*-1 ||cust.region end asc,
case when cust.sort_value= 2 then cust.region asc``````
How could I be able to do the sorting based on the requirement that if sort_value = 1 I need the order by in this order: cust.id asc, cust.name desc, cust.region asc
• 1. Re: decode for order by combined with ass and desc
``````select  cust.id,
cust.name,
cust.region
from  customer cust
order by cust.sort_value,
case cust.sort_value
when 1 then cust.id
end asc,
case cust.sort_value
when 1 then cust.name
end desc,
cust.region asc
/``````
SY.
• 2. Re: decode for order by combined with ass and desc
Hi,

So you always want to sort by region, but sometimes you want to sort by other things first.
Here's one way to do that:
``````ORDER BY   CASE
WHEN  cust.sort_value = 1
THEN  cust.id
END                    ASC
,          CASE
WHEN  cust.sort_value = 1
THEN  cust.name
END                    DESC
,        cust.region               ASC``````
• 3. Re: decode for order by combined with ass and desc
Thank you!
• 4. Re: decode for order by combined with ass and desc
Thank you!