Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle dynamic DESC and ASC in order by

Tags:

sql

oracle

Order by is dynamic but the sort order is static.

SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN OFFER_PRICE END DESC,
         CASE WHEN InputParam = 'ENDING SOON' THEN EXPIRY_DATE END DESC, 
         CASE WHEN InputParam = 'DISCOUNT' THEN DISC_PERCENTAGE END DESC,
         CASE WHEN InputParam = 'SAVING' THEN SAVING END DESC

Now I need to make sure that the sort order is also dynamic. Is there some way to make sort order dynamic in the above query?

like image 825
Aseem Gautam Avatar asked Feb 10 '11 10:02

Aseem Gautam


People also ask

What is the order of execution of SQL query in Oracle?

This may help you. When a query is submitted to the database, it is executed in the following order: FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY clause So why is it important to understand this?

What is the default order of sorting data in Oracle?

By default, the ORDER BY clause sorts rows in ascending order whether you specify ASC or not. If you want to sort rows in descending order, you use DESC explicitly.

Does order of WHERE clause matter in Oracle?

The order is of little consequence.

What is the position for ORDER BY clause in SELECT statement in Oracle SQL?

If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list.


2 Answers

If you also want to make the sort order (ASC/DESC) dynamic, you could do the following:

SELECT ...
Order By CASE WHEN InputParam = 'PRICE' THEN l_so * OFFER_PRICE END,
         CASE WHEN InputParam = 'ENDING SOON' 
              THEN l_so * (SYSDATE - EXPIRY_DATE) END, 
         CASE WHEN InputParam = 'DISCOUNT' THEN l_so * DISC_PERCENTAGE END,
         CASE WHEN InputParam = 'SAVING' THEN l_so * SAVING END

with a variable l_so that contains 1 or -1 depending upon which sort order you want.

like image 126
Vincent Malgrat Avatar answered Nov 05 '22 11:11

Vincent Malgrat


This works for me:

order by 
  case when :dir_param = 'ASC' then
    case :col_param 
      when 'col_1_identifier' then col_1_name
      when 'col_2_identifier' then col_2_name
      ...
    end
  end,
  case when :dir_param = 'DSC' then
    case :col_param 
      when 'col_1_identifier' then col_1_name
      when 'col_2_identifier' then col_2_name
      ...
    end
  end desc

or

order by 
case when :dir_param = 'ASC' and :col_param = 'col_1_identifier' then col_1_name end,
case when :dir_param = 'DSC' and :col_param = 'col_1_identifier' then col_1_name end desc,
case when :dir_param = 'ASC' and :col_param = 'col_2_identifier' then col_2_name end,
case when :dir_param = 'DSC' and :col_param = 'col_2_identifier' then col_2_name end desc

replace literals, variable and column names with those specific to your situation. Oracle seemed to be very picky about the placement of the desc sort direction qualifier.

like image 12
Yale Madden Avatar answered Nov 05 '22 12:11

Yale Madden