8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL for Beginners (Part 4) : The ORDER BY Clause
This is the fourth part of a series of articles showing the basics of SQL. In this article we take a look at how you can influence the order of the data that is returned by queries.
- Setup
- Introduction
- Identify Columns : Expression, Position and Column Alias
- Ascending (ASC) and Descending (DESC) Order
- Handling NULLs : NULLS FIRST and NULLS LAST
Related articles.
- SQL for Beginners (Part 4) : The ORDER BY Clause
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
Setup
You can perform all these queries online for free using SQL Fiddle.
The examples in this article require the following tables to be present.
--DROP TABLE employees PURGE; --DROP TABLE departments PURGE; CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(14), location VARCHAR2(13) ); INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO departments VALUES (20,'RESEARCH','DALLAS'); INSERT INTO departments VALUES (30,'SALES','CHICAGO'); INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON'); COMMIT; CREATE TABLE employees ( employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, employee_name VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id) ); INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
Introduction
The best place to start, is with this quote from the documention (here).
"Use the ORDER BY
clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order."
When you are beginning to learn SQL, it is very easy to forget this and start to believe there is a predictable pattern to the output of data. There are a couple of reasons this might occur.
- If you are using small amounts of test data, all the rows may be loaded into a single block, so the rows may well get returned in a predictable manner. Once you graduate to live data, your assumption may be proved wrong.
- There may be an implicit sort performed by one or more of the operations in your query, making the output predictable, but that may only be for the specific version and possibly patch version you are running. Oracle may change the algorithm at any point in the future, which could "break" your application. Oracle told you how to protect yourself in the above statement.
The documentation uses the following text description for the ORDER BY
clause.
ORDER [ SIBLINGS ] BY { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]...
The following sections will demonstrate some of these options.
Identify Columns : Expression, Position and Column Alias
There are three main ways to identify the columns included in the sort operation. Probably the most common expression is to specify one or more columns in a comma separated list. In the following example, the results are ordered by the SALARY and COMMISSION columns.
SELECT e.salary, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary, e.commission; SALARY COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 500 WARD 1250 1400 MARTIN 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
Amongst other things, expressions can include multiple columns combined together. In the following example, the results are order by the sum of the SALARY and COMMISSION columns. The NVL
function converts any NULL values in the COMMISSION column to zero, to make the result of the addition more obvious.
SELECT e.salary, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary + NVL(e.commission,0); SALARY COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1500 0 TURNER 1250 500 WARD 1600 300 ALLEN 1250 1400 MARTIN 2850 BLAKE 6 rows selected. SQL>
Columns can be referenced by their column position. Remember, if you alter the SELECT
list, you will have to amend the ORDER BY
clause also.
SELECT e.salary, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY 1; SALARY COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 500 WARD 1250 1400 MARTIN 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
Columns can also be referenced by their column alias. In the following example, the SALARY column is aliased to SAL, which is used in the ORDER BY
clause.
SELECT e.salary AS sal, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY sal; SAL COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 500 WARD 1250 1400 MARTIN 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
Ascending (ASC) and Descending (DESC) Order
The default order is ascending, so the following statements are functionally equivalent.
-- Ascending (ASC) by default. SELECT e.salary AS sal, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary; SAL COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 500 WARD 1250 1400 MARTIN 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL> -- Explicitly setting ASC. SELECT e.salary AS sal, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary ASC; SAL COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 500 WARD 1250 1400 MARTIN 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
To switch to descending, use the DESC
keyword.
SELECT e.salary AS sal, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary DESC; SAL COMMISSION EMPLOYEE_N ---------- ---------- ---------- 2850 BLAKE 1600 300 ALLEN 1500 0 TURNER 1250 500 WARD 1250 1400 MARTIN 950 JAMES 6 rows selected. SQL>
Each column in the ORDER BY
clause can have a different order.
SELECT e.salary, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary ASC, e.commission DESC; SALARY COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 1400 MARTIN 1250 500 WARD 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
Handling NULLs : NULLS FIRST and NULLS LAST
An ascending order assumes NULLS LAST
. You can specify it explicitly if you want to.
SELECT e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.commission ASC; COMMISSION EMPLOYEE_N ---------- ---------- 0 TURNER 300 ALLEN 500 WARD 1400 MARTIN JAMES BLAKE 6 rows selected. SQL>
The default can be altered by specifying NULLS FIRST
.
SELECT e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.commission ASC NULLS FIRST; COMMISSION EMPLOYEE_N ---------- ---------- JAMES BLAKE 0 TURNER 300 ALLEN 500 WARD 1400 MARTIN 6 rows selected. SQL>
A descending order assumes NULLS FIRST
. You can specify it explicitly if you want to.
SELECT e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.commission DESC; COMMISSION EMPLOYEE_N ---------- ---------- BLAKE JAMES 1400 MARTIN 500 WARD 300 ALLEN 0 TURNER 6 rows selected. SQL>
The default can be altered by specifying NULLS LAST
.
SELECT e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.commission DESC NULLS LAST; COMMISSION EMPLOYEE_N ---------- ---------- 1400 MARTIN 500 WARD 300 ALLEN 0 TURNER JAMES BLAKE 6 rows selected. SQL>
Each column in the ORDER BY
clause can have different NULL handling.
SELECT e.salary, e.commission, e.employee_name FROM employees e WHERE department_id = 30 ORDER BY e.salary ASC NULLS FIRST, e.commission DESC NULLS LAST; SALARY COMMISSION EMPLOYEE_N ---------- ---------- ---------- 950 JAMES 1250 1400 MARTIN 1250 500 WARD 1500 0 TURNER 1600 300 ALLEN 2850 BLAKE 6 rows selected. SQL>
For more information see:
- SQL for Beginners (Part 4) : The ORDER BY Clause
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Database SQL Language Reference : SELECT
Hope this helps. Regards Tim...