I have a query and i would like to make two kinds of order based on a condition.
For example, if a field is NULL, i need to make an order and if it is not, i have to make another order. How i can do it ?
select *
from table_1 t
order by (if t.field1 is null then
order by t.field2 DESC, field3 ASC
else
order by t.field4 ASC, field5 DESC)
This is a sample code: i want to make different order (ASC/DESC and different columns), based on the value of FIELD1
EXAMPLE
CONDITIONAL
ID FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
1 1 2 3 4 5
2 NULL 6 7 8 9
DATA
ID PARENT_ID DATA1 DATA2 DATA3
1 1 X Y J
2 1 Z W U
3 2 XY YX O
4 2 ZW WZ I
select d.*
from data d, conditional c
where d.parent_id = c.id
and d.parent_id = 1
order by
case
when c.field1 is null then
data1 asc, data2 desc
else
data3 asc, data1 desc
end
In this example i select the DATA rows ONE and TWO (the rows that have parent id = 1). Now that i have make this decision, i want to orther the DATA columns based on the value of CONDICTIONAL.FIELD1 column. I hope now is more clean.
Sure this query doesent work but this is "what i will need."
You can have CASE
construct in the ORDER BY
clause :
SQL> SELECT * FROM EMP T
2 ORDER BY
3 CASE
4 WHEN COMM IS NULL
5 THEN SAL
6 ELSE EMPNO
7 END
8 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
14 rows selected.
NOTE : Take care of the DATA TYPE
of the columns in the ORDER BY
clause.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With