Oracle SQL - ORDER BY Clause

Introduction

The result of a query is a table with a set of rows.

The order is not guaranteed to be the same for each query.

To insist on getting the resulting rows of your query back in a guaranteed order, use the ORDER BY clause in your SELECT command statements.

You can specify multiple sort specifications, separated by commas.

Each sort specification consists of a column specification or column expression, optionally followed by the keyword DESC (descending), in case you want to sort in descending order.

Without this addition, the default sorting order is ASC (ascending).

You can use ASC to denote that it is ascending order.

The column specification may consist of a single column name or a column expression.

To refer to columns in the ORDER BY clause, you can use any of the following:

  • Regular column names
  • Column aliases defined in the SELECT clause, useful in case of complex expressions in the SELECT clause
  • Column ordinal numbers

Column ordinal numbers in the ORDER BY clause have no relationship with the order of the columns in the database.

They are dependent on only the SELECT clause of your query.

Using column aliases instead increases SQL statement readability, and ensures your ORDER BY clauses become independent of the SELECT clauses of your queries.

The following code shows how you can sort query results on column combinations.

As you can see, the query result is sorted on department number, and then on employee name for each department.

select deptno, ename, init, msal 
from   emp 
where  msal < 1500 
order  by deptno, ename; 
  

You can reverse the default sorting order by adding the DESC keyword to your ORDER BY clause.

select ename, 12*msal+comm as yearsal 
from   emp 
where  job = 'SALESREP' 
order  by yearsal desc; 

Demo

SQL>
SQL> drop table emp;

Table dropped.--  ww  w . ja  v  a  2 s . c om

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> select deptno, ename, init, msal
  2  from   emp
  3  where  msal < 1500
  4  order  by deptno, ename;

   DEPTNO | ENAME    | INIT  |      MSAL
--------- | -------- | ----- | ---------
    1     | MARY     | ABC   |  01300.00
    1     | WARD     | TF    |  01250.00
    2     | ADAMS    | AA    |  01100.00
    30    | BROWN    | P     |  01250.00

SQL>
SQL> select ename, 12*msal+comm as yearsal
  2  from   emp
  3  where  job = 'SALESREP'
  4  order  by yearsal desc;

ENAME    |   YEARSAL
-------- | ---------
ALLEN    |  19500.00
BREAD    |  18000.00
BROWN    |  16400.00
WARD     |  15500.00

SQL>

Related Topics