DBA Hub

📋Steps in this guide1/5

SQL for Beginners (Part 4) : The ORDER BY Clause

In this article we take a look at how you can influence the order of the data that is returned by queries.

oracle miscconfigurationintermediate
by OracleDba
12 views
1

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--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;
2

Introduction

The best place to start, is with this quote from the documention ( here ). > "Use the 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 clause. The following sections will demonstrate some of these options.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
  [, { expr | position | c_alias }
     [ ASC | DESC ]
     [ NULLS FIRST | NULLS LAST ]
  ]...
3

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. 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 function converts any NULL values in the COMMISSION column to zero, to make the result of the addition more obvious. Columns can be referenced by their column position. Remember, if you alter the list, you will have to amend the clause also. 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 clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
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>

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>

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>

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>
4

Ascending (ASC) and Descending (DESC) Order

The default order is ascending, so the following statements are functionally equivalent. To switch to descending, use the keyword. Each column in the clause can have a different order.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- 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>

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>

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>
5

Handling NULLs : NULLS FIRST and NULLS LAST

An ascending order assumes . You can specify it explicitly if you want to. The default can be altered by specifying . A descending order assumes . You can specify it explicitly if you want to. The default can be altered by specifying . Each column in the clause can have different NULL handling. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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>

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>

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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!