Oracle sort one — null value processing

Time:2021-4-12

In daily query operation, we may need all the null values in a field to be ranked first or last. Null values can be ranked last in ascending order or first in descending order. For example, when the Mgr field in the EMP table has a null value and order by is implemented through nulls first and nulls last, the query statement is as follows:

  

SQL> SELECT ename ,mgr FROM emp WHERE deptno=10 ORDER BY mgr ASC;

ENAME MGR
———- —–
MILLER 7782
CLARK 7839
KING

 

SQL> SELECT ename ,mgr FROM emp WHERE deptno=10 ORDER BY mgr ASC NULLS FIRST;
ENAME MGR
———- —–
KING 
MILLER 7782
CLARK 7839

————————————————————————————————————————————————-

 

SQL> SELECT ename ,mgr FROM emp WHERE deptno=10 ORDER BY mgr DESC;

ENAME MGR
———- —–
KING 
CLARK 7839
MILLER 7782

 

SQL> SELECT ename ,mgr FROM emp WHERE deptno=10 ORDER BY mgr DESC NULLS FIRST;
ENAME MGR
———- —–
KING 
CLARK 7839
MILLER 7782

 

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]