This discussion is archived
4 Replies Latest reply: Nov 20, 2012 4:41 AM by 567822 RSS

decode for order by combined with ass and desc

567822 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points