Recipe2.5.Dealing with Nulls when Sorting
Recipe 2.5. Dealing with Nulls when SortingProblemYou 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 SolutionDepending 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 ServerUse 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 OracleUsers 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 DiscussionUnless 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. |
EAN: 2147483647
Pages: 235