Thanks for the question, suresh.

Asked: May 19, 2008 - 1:42 pm UTC

Last updated: December 11, 2018 - 3:07 pm UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

Hi,

Query 1:

SELECT * FROM
(SELECT 10 a,20 b FROM dual
UNION
SELECT 50,30 FROM dual)
ORDER BY CASE 1
WHEN 1 THEN a
ELSE b
END DESC;

output:
A B
------
50 30
10 20

Query 2:

SELECT * FROM
(SELECT 10 a,20 b FROM dual
UNION
SELECT 50,30 FROM dual)
ORDER BY CASE 1
WHEN 1 THEN 1--first column
ELSE 2--second column
END DESC;

output:
A B
------
10 20
50 30

Question1: Why second query is not returning resuts by descending order?
Question2: What is the best way to do dynamic sort? Using CASE or SYS_CONTEXT or....?

Thanks in advance.

and Tom said...

you ordered by the equivalent of:

order by case 1 when 1 then 'A' else 'B' end desc

you are ordering by a CONSTANT.

you might want to:

order by case when :input = 1 then a end DESC, 
         case when :input = 2 then b end DESC
/

sort of like this:


ops$tkyte%ORA10GR2> variable input number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :input := 1

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM
  2  (SELECT 10 a,30 b FROM dual UNION SELECT 50,20 FROM dual)
  3  ORDER BY case when :input = 1 then a end DESC,
  4           case when :input = 2 then b end DESC
  5  /

         A          B
---------- ----------
        50         20
        10         30

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec :input := 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> /

         A          B
---------- ----------
        10         30
        50         20

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

suresh, May 20, 2008 - 6:20 pm UTC

Hi,
Thanks for your clarification.

From any table, we can sort by column number. Following query will return results descending order by first column on the table, EMP.
Ex: select * from EMP order by 1 desc

Why same concept is not working if I use SORT with CASE statment?

SELECT * FROM
(SELECT 10 a,30 b FROM dual
UNION
SELECT 50,20 FROM dual)
ORDER BY case when :input = 1 then a end DESC,
case when :input = 2 then b end DESC

exec :input := 1;
output
A B
-----
50 20
10 30

exec :input :=2;
output
A B
------
10 30
50 20

SELECT * FROM
(SELECT 10 a,30 b FROM dual
UNION
SELECT 50,20 FROM dual)
ORDER BY case when :input = 1 then 1 end DESC,--sort by first column(positon 1) i.e. A
case when :input = 2 then 2 end DESC--sort by second column(position 2) i.e. B

exec :input := 1;
output
A B
-----
10 30
50 20
####### Not working #########

SELECT * FROM
(SELECT 10 a,30 b FROM dual
UNION
SELECT 50,20 FROM dual)
ORDER BY 1 DESC

output
A B
-----
50 20
10 30
####### working #########

Thanks in advance.

May 20, 2008 - 9:52 pm UTC

you did not get it:

ORDER BY case when :input = 1 then 1 end DESC,--sort by first column(positon 1) 
i.e. A
         case when :input = 2 then 2 end DESC--sort by second column(position 
2) i.e. B

is the SAME as, IDENTICAL to:

ORDER BY case when :input = 1 then 'A' end DESC,--sort by first column(positon 1) 
i.e. A
         case when :input = 2 then 'B' end DESC--sort by second column(position 
2) i.e. B

you cannot dynamically return a column position, you dynamically returned A CONSTANT, A NUMBER, A CONSTANT VALUE - JUST A VALUE

order by <ordinal position> | <expression>

you are returning an expression - not an ordinal position.

Can I program the ASC/DESC parameter too?

Gary, December 10, 2018 - 8:20 pm UTC

Hi Guys

I understand how the original solution works:

2 (SELECT 10 a,30 b FROM dual UNION SELECT 50,20 FROM dual)
3 ORDER BY case when :input = 1 then a end DESC,
4 case when :input = 2 then b end DESC

I was hoping to be able to control the ASC/DESC part of the ORDER BY too. I've tried several different formats but can't seem to get Oracle to like my attempts.

I'd like to have a variable similar to ':input' above that would control whether we sorted ASCENDING or DESCENDING.

I tried using another set of CASE statements in different places around the existing ones but nothing worked.

Can you please help with the syntax for that?

Thanks very much!!

-gary

December 11, 2018 - 3:07 pm UTC

You can't bind the direction (asc/desc) of the sort. As a workaround you can have an ascending sort and a descending one.

These return the column if the sort variable matches asc/desc as requested. Otherwise null:

var srt varchar2(4);

exec :srt := 'asc';
with rws as (
  select level r from dual
  connect by level <= 3
)
  select * from rws
  order  by case :srt
    when 'asc' then r
  end,
  case :srt
    when 'desc' then r
  end desc;

R   
  1 
  2 
  3 
  
exec :srt := 'desc';

with rws as (
  select level r from dual
  connect by level <= 3
)
  select * from rws
  order  by case :srt
    when 'asc' then r
  end,
  case :srt
    when 'desc' then r
  end desc;

R   
  3 
  2 
  1

Flip sign if desc

Dan, December 11, 2018 - 5:43 pm UTC

If your sort column is a number, you can always sort asc, but flip the sign conditionally...

order by 
        case when sort_order= 'D' then -1 else 1 end * my_numeric_column

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Can I program the ASC/DESC parameter too?

Flip sign if desc

More to Explore

PL/SQL demos

PL/SQL docs

Dynamic sort order

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Can I program the ASC/DESC parameter too?

Flip sign if desc

More to Explore

PL/SQL demos

PL/SQL docs