SQL ORDER BY Clause

Whenever we execute a select query on the database, based on the conditions used in the query oracle returns the result. But there is no guarantee of the order of the rows. The order of the rows appear in the result set depends on the operations chosen by the optimizer and access paths chosen by the optimizer to produce the required output.

But if we have requirement of guaranteed order of resulting rows, then we have to use SQL ORDER BY clause in SELECT statement.

Below is the syntax for ORDER BY clause.

SELECT <<column_names>>
FROM <<table_names>>
ORDER BY <<order by list>> [ASC/DESC]

In ORDER BY Clause we can specify multiple sort operations separated by commas. Also we can sort the result in ascending or descending order. To sort the result in ascending order we can specify ASC keyword. By default Oracle sorts the result in ascending order that means it is not necessary to write a ASC keyword after ORDER BY clause. To sort the result in descending order, we need to specify DESC keyword after ORDER BY clause.

In ORDER BY clause we can specify order by columns in following ways:

  1. Regular column names
  2. Column alias used in the SELECT clause
  3. Column ordinal numbers

Below query returns the default result set as returned by the oracle to the client because we have not used any ORDER BY clause.

SQL> select STUDENT_ID,FIRST_NAME,LAST_NAME,PHONE,REGISTRATION_DATE 
	 FROM STUDENT_INFO;

STUDENT_ID FIRST_NAME LAST_NAME  PHONE           REGISTRAT
---------- ---------- ---------- --------------- ---------
       102 Fred       Crocitto   718-555-5555    22-JAN-07
       103 J.         Landry     201-555-5555    22-JAN-07
       104 Laetia     Enison     718-555-5555    22-JAN-07
       105 Angel      Moskowitz  201-555-5555    22-JAN-07
       106 Judith     Olvsade    201-555-5555    22-JAN-07
       107 Catherine  Mierzwa    718-555-5555    22-JAN-07
       108 Judy       Sethi      617-555-5555    22-JAN-07
       109 Larry      Walter     718-555-5555    22-JAN-07
       110 Maria      Martin     718-555-5555    25-JAN-07
       111 Peggy      Noviello                   25-JAN-07
       112 Thomas     Thomas     201-555-5555    25-JAN-07
       113 Anil       Kulina     718-555-5555    25-JAN-07

12 rows selected.

Now, if we require the student data which will be in ascending order of Students FIRST_NAME then we can write the query as shown below.

SQL> select STUDENT_ID,FIRST_NAME,LAST_NAME,PHONE,REGISTRATION_DATE 
	 FROM STUDENT_INFO 
	 ORDER BY FIRST_NAME;

STUDENT_ID FIRST_NAME LAST_NAME  PHONE           REGISTRAT
---------- ---------- ---------- --------------- ---------
       105 Angel      Moskowitz  201-555-5555    22-JAN-07
       113 Anil       Kulina     718-555-5555    25-JAN-07
       107 Catherine  Mierzwa    718-555-5555    22-JAN-07
       102 Fred       Crocitto   718-555-5555    22-JAN-07
       103 J.         Landry     201-555-5555    22-JAN-07
       106 Judith     Olvsade    201-555-5555    22-JAN-07
       108 Judy       Sethi      617-555-5555    22-JAN-07
       104 Laetia     Enison     718-555-5555    22-JAN-07
       109 Larry      Walter     718-555-5555    22-JAN-07
       110 Maria      Martin     718-555-5555    25-JAN-07
       111 Peggy      Noviello                   25-JAN-07
       112 Thomas     Thomas     201-555-5555    25-JAN-07

12 rows selected.

If we require the student data which will be in descending order of Students FIRST_NAME then we can write the query as shown below.

SQL> select STUDENT_ID,FIRST_NAME,LAST_NAME,PHONE,REGISTRATION_DATE 
	 FROM STUDENT_INFO 
	 ORDER BY FIRST_NAME DESC;

STUDENT_ID FIRST_NAME LAST_NAME  PHONE           REGISTRAT
---------- ---------- ---------- --------------- ---------
       112 Thomas     Thomas     201-555-5555    25-JAN-07
       111 Peggy      Noviello                   25-JAN-07
       110 Maria      Martin     718-555-5555    25-JAN-07
       109 Larry      Walter     718-555-5555    22-JAN-07
       104 Laetia     Enison     718-555-5555    22-JAN-07
       108 Judy       Sethi      617-555-5555    22-JAN-07
       106 Judith     Olvsade    201-555-5555    22-JAN-07
       103 J.         Landry     201-555-5555    22-JAN-07
       102 Fred       Crocitto   718-555-5555    22-JAN-07
       107 Catherine  Mierzwa    718-555-5555    22-JAN-07
       113 Anil       Kulina     718-555-5555    25-JAN-07
       105 Angel      Moskowitz  201-555-5555    22-JAN-07

12 rows selected.

In the above result, we can see that column PHONE is having NULL value for student ID 111. One question may be arise in your mind that how NULL values will behave while sorting the result. Note that, oracle treats the NULL values as high values so the default behaviour is

  • NULLS LAST is default for ASC
  • NULLS FIRST is default for DESC

Below query sorts the result in ascending order for PHONE column. We can see that NULL value row is appearing as the LAST row.

SQL> select STUDENT_ID,FIRST_NAME,LAST_NAME,PHONE,REGISTRATION_DATE 
	 FROM STUDENT_INFO 
	 ORDER BY PHONE;

STUDENT_ID FIRST_NAME LAST_NAME  PHONE           REGISTRAT
---------- ---------- ---------- --------------- ---------
       112 Thomas     Thomas     201-555-5555    25-JAN-07
       106 Judith     Olvsade    201-555-5555    22-JAN-07
       105 Angel      Moskowitz  201-555-5555    22-JAN-07
       103 J.         Landry     201-555-5555    22-JAN-07
       108 Judy       Sethi      617-555-5555    22-JAN-07
       110 Maria      Martin     718-555-5555    25-JAN-07
       109 Larry      Walter     718-555-5555    22-JAN-07
       113 Anil       Kulina     718-555-5555    25-JAN-07
       104 Laetia     Enison     718-555-5555    22-JAN-07
       102 Fred       Crocitto   718-555-5555    22-JAN-07
       107 Catherine  Mierzwa    718-555-5555    22-JAN-07
       111 Peggy      Noviello                   25-JAN-07

12 rows selected.

Below query sorts the result in descending order for PHONE column. We can see that NULL value row is appearing as the FIRST ROW.

SQL> select STUDENT_ID,FIRST_NAME,LAST_NAME,PHONE,REGISTRATION_DATE 
	 FROM STUDENT_INFO 
	 ORDER BY PHONE DESC;

STUDENT_ID FIRST_NAME LAST_NAME  PHONE           REGISTRAT
---------- ---------- ---------- --------------- ---------
       111 Peggy      Noviello                   25-JAN-07
       104 Laetia     Enison     718-555-5555    22-JAN-07
       102 Fred       Crocitto   718-555-5555    22-JAN-07
       107 Catherine  Mierzwa    718-555-5555    22-JAN-07
       110 Maria      Martin     718-555-5555    25-JAN-07
       109 Larry      Walter     718-555-5555    22-JAN-07
       113 Anil       Kulina     718-555-5555    25-JAN-07
       108 Judy       Sethi      617-555-5555    22-JAN-07
       103 J.         Landry     201-555-5555    22-JAN-07
       112 Thomas     Thomas     201-555-5555    25-JAN-07
       106 Judith     Olvsade    201-555-5555    22-JAN-07
       105 Angel      Moskowitz  201-555-5555    22-JAN-07

12 rows selected.

Leave a Reply