8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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.

Related articles.

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.

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:

Hope this helps. Regards Tim...

Back to the Top.