Geek Answers Handbook

Oracle is ordered for different conditions

I have a request, and I would like to make two types of orders based on the condition.

For example, if the field is NULL, I need to place an order, and if not, I have to place another order. How can i do this?

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 an example code: I want to do a 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 ONE and TWO rows (rows with parent id = 1). Now that I have made this decision, I want DATA columns or columns to be based on the value of the CONDICTIONAL.FIELD1 column. Hopefully it will be cleaner now.

I am sure that this request works, but it is "what I need."

+4
source share
2 answers

CASE ORDER BY:

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.

. DATA TYPE ORDER BY.

+7

, case order by. asc/desc , case, :

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 d.data1 else d.data3 end asc,
       case when c.field1 is null then d.data2 else d.data1 end desc;

, :

        ID  PARENT_ID DATA1 DATA2 DATA3
---------- ---------- ----- ----- -----
         1          1 X     Y     J     
         2          1 Z     W     U

select *:

select d.id, d.parent_id, d.data1, d.data2, d.data3
  from data d
  join conditional c
    on c.id = d.parent_id
 where d.parent_id = 1
order by
       case when c.field1 is null then d.data1 else d.data3 end asc,
       case when c.field1 is null then d.data2 else d.data1 end desc;

SQL-.

+4

More articles:


All Articles