ORDER BY CLAUSE

It is possible to display the rows of a table in the required order using ORDER BY clause. It is used to sort rows on the given column(s) and in the given order at the time of retrieving rows. Remember, sorting takes place on the row that are retrieved and in no way affects the rows in the table. That means the order of the rows will remain unchanged.

Note: ORDER BY must always be the last of all clauses used in the SELECT command.

The following SELECT command displays the rows after sorting rows on course fee.

 select name, fee from courses order by fee;

NAME                      FEE
 -------------------- ---------
C programming         3500
XML Programming  4000
Oracle database        4500
Java Language         4500
ASP.NET                 5000
VB.NET                  5500

Note: Null values are placed at the end in ascending order and at the beginning in descending order.

The default order for sorting is ascending. Use option DESC to sort in the descending order. It is also possible to sort on more than one column. To sort rows of COURSES table in the ascending order of DURATION and descending order of FEE, enter:

select name, duration, fee from courses
order by duration , fee desc;

NAME              DURATION              FEE
-------------------- --------- ---------
XML Programming 15                      4000
C programming        20                      3500
ASP.NET                 25                      5000
Oracle database       25                      4500
Java Language         25                     4500
VB.NET                  30                      5500

First, all rows are sorted in the ascending order of DURATION column. Then the rows that have same value in DURATION column will be further sorted in the descending order of FEE column.

Using column position 

Instead of giving the name of the column, you can also give the position of the column on which you want to sort rows.

For example, the following SELECT sorts rows based on discount to be given to each course.

select name, fee, fee * 0.15
from courses
order by 3;

NAME         FEE            FEE*0.15
 -------------------- --------- ---------
C                 3500            525
XML          4000             600
Oracle        4500             675
Java            4500            675
ASP.NET   5000            750
VB.NET     5500            825

Note: Column position refers to position of the column in the selected columns and not the position of the column in the table.

The above command uses column position in ORDER BY clause. Alternatively you can use column alias in ORDER BY clause as follows:

select name, fee, fee * 0.15 discount
from courses
order by discount;

NAME        FEE              DISCOUNT
-------------------- --------- ---------
C                3500              525
XML          4000              600
Oracle        4500              675
Java            4500              675
ASP.NET   5000              750
VB.NET     5500              825