Problem
You want to sort results from EMP by COMM, but the field is nullable. You need a way to specify whether nulls sort last:
ENAME SAL COMM
---------- ---------- ----------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
or whether they sort first:
ENAME SAL COMM
---------- ---------- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
Solution
Depending on how you want the data to look (and how your particular RDBMS sorts NULL values), you can sort the nullable column in ascending or descending order:
1 select ename,sal,comm
2 from emp
3 order by 3
1 select ename,sal,comm
2 from emp
3 order by 3 desc
This solution puts you in a position such that if the nullable column contains non-NULL values, they will be sorted in ascending or descending order as well, according to what you ask for; this may or may not what you have in mind. If instead you would like to sort NULL values differently than non-NULL values, for example, you want to sort non-NULL values in ascending or descending order and all NULL values last, you can use a CASE expression to conditionally sort the column.
DB2, MySQL, PostgreSQL, and SQL Server
Use a CASE expression to "flag" when a value is NULL. The idea is to have a flag with two values: one to represent NULLs, the other to represent non-NULLs. Once you have that, simply add this flag column to the ORDER BY clause. You'll easily be able to control whether NULL values are sorted first or last without interfering with non-NULL values:
/* NON-NULL COMM SORTED ASCENDING, ALL NULLS LAST */
1 select ename,sal,comm
2 from (
3 select ename,sal,comm,
4 case when comm is null then 0 else 1 end as is_null
5 from emp
6 ) x
7 order by is_null desc,comm
ENAME SAL COMM
------ ----- ----------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
/* NON-NULL COMM SORTED DESCENDING, ALL NULLS LAST */
1 select ename,sal,comm
2 from (
3 select ename,sal,comm,
4 case when comm is null then 0 else 1 end as is_null
5 from emp
6 ) x
7 order by is_null desc,comm desc
ENAME SAL COMM
------ ----- ----------
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
/* NON-NULL COMM SORTED ASCENDING, ALL NULLS FIRST */
1 select ename,sal,comm
2 from (
3 select ename,sal,comm,
4 case when comm is null then 0 else 1 end as is_null
5 from emp
6 ) x
7 order by is_null,comm
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
/* NON-NULL COMM SORTED DESCENDING, ALL NULLS FIRST */
1 select ename,sal,comm
2 from (
3 select ename,sal,comm,
4 case when comm is null then 0 else 1 end as is_null
5 from emp
6 ) x
7 order by is_null,comm desc
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
Oracle
Users on Oracle8i Database and earlier can use the solution for the other platforms. Users on Oracle9i Database and later can use the NULLS FIRST and NULLS LAST extension to the ORDER BYclause to ensure NULLs are sorted first or last regardless of how non-NULL values are sorted:
/* NON-NULL COMM SORTED ASCENDING, ALL NULLS LAST */
1 select ename,sal,comm
2 from emp
3 order by comm nulls last
ENAME SAL COMM
------ ----- ---------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
/* NON-NULL COMM SORTED ASCENDING, ALL NULLS FIRST */
1 select ename,sal,comm
2 from emp
3 order by comm nulls first
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
/* NON-NULL COMM SORTED DESCENDING, ALL NULLS FIRST */
1 select ename,sal,comm
2 from emp
3 order by comm desc nulls first
ENAME SAL COMM
------ ----- ----------
SMITH 800
JONES 2975
CLARK 2450
BLAKE 2850
SCOTT 3000
KING 5000
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
MARTIN 1250 1400
WARD 1250 500
ALLEN 1600 300
TURNER 1500 0
Discussion
Unless your RDBMS provides you with a way to easily sort NULL values first or last without modifying non-NULL values in the same column (such as Oracle does), you'll need an auxiliary column.
The purpose of this extra column (in the query only, not in the table) is to allow you to identify NULL values and sort them altogether, first or last. The following query returns the result set for inline view X for the non-Oracle solution:
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp
ENAME SAL COMM IS_NULL
------ ----- ---------- ----------
SMITH 800 0
ALLEN 1600 300 1
WARD 1250 500 1
JONES 2975 0
MARTIN 1250 1400 1
BLAKE 2850 0
CLARK 2450 0
SCOTT 3000 0
KING 5000 0
TURNER 1500 0 1
ADAMS 1100 0
JAMES 950 0
FORD 3000 0
MILLER 1300 0
By using the values returned by IS_NULL, you can easily sort NULLS first or last without interfering with the sorting of COMM.
** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table
0 Comments:
Post a Comment