SQL ORDER BY Clause

We know that SQL SELECT Statement returns the rows in no particular order. With the help of ORDER BY clause, we can order the rows in a desired order. ORDER BY clause is used to sort the returned records in an order. By using ORDER BY clause, we can sort the result in ascending or descending order. This clause can be used with multiple columns as well.

ORDER BY Syntax

SELECT column_name1, column_name2, column_name3.... 
FROM table_name
WHERE Condition 
ORDER BY column_name1, column_name2, .... (ASC OR DESC);

Here ASC is used for sorting in ascending order and DESC for descending order.

SQL ORDER BY clause example

Lets say we have a table “STUDENT” with the following records.

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

The following SQL statement will fetch all the records from the table “STUDENT” and the returned rows will be in ascending order of student age.

SELECT * 
   FROM STUDENT
ORDER BY AGE;

Note: The default order of sorting is ascending so the rows will be sorted based on the column “AGE”, even though we have not used the ASC keyword in order by clause. So it is safe to say that the following query is same as the above query and would fetch the same result.

SELECT * 
   FROM STUDENT
ORDER BY AGE ASC;

Result:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
+---------+----------+-----+-----------+----------+

SQL ORDER BY DESC example

Table: STUDENT

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

The following SQL statement will fetch the student names from the table “STUDENT” and the returned names will be sorted in descending order (we have used DESC for descending order in the ORDER BY clause).

SELECT NAME 
   FROM STUDENT
ORDER BY NAME DESC;

Result:

+----------+
| NAME     | 
+----------+
| Steve    | 
| Rick     |  
| Richard  |  
| Peter    | 
| Mark     |  
| Kate     | 
| Brad     |  
+----------+

As you can see, we have got a list of student names sorted in the descending order.

SQL ORDER BY Multiple Columns

In the above examples, we have done the sorting based on a single column. However we can use multiple columns in ORDER BY clause.

When multiple columns are used in ORDER BY, first the rows will be sorted based on the first column and then by the second column. Lets take an example to understand this.

Table: EMPLOYEE

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1001     Paul       India    Noida   201310
1005     Raul       India    Agra    283126
1002     Ajeet      India    Noida   201301
1003     Jon        India    Agra    282008
1006     David      India    Agra    282005
1004     Chetan     India    Noida   201313

Sorting the rows based on the city first and then by pin code within each city:

SELECT * 
   FROM EMPLOYEE
ORDER BY CITY, PINCODE;

Result:
As you can see that the rows are sorted by CITY and then within city sorted by pincode.

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1006     David      India    Agra    282005
1003     Jon        India    Agra    282008
1005     Raul       India    Agra    283126
1002     Ajeet      India    Noida   201301
1001     Paul       India    Noida   201310
1004     Chetan     India    Noida   201313

Sorting the rows based on the city first in ascending order and then by pin code in descending order within each city:

SELECT * 
   FROM EMPLOYEE
ORDER BY CITY ASC, PINCODE DESC;

Result:
As you can see that the rows are sorted by CITY in ascending order and then within city sorted in reverse order by pincode.

EMP_ID   EMP_NAME  COUNTRY   CITY   PINCODE
------   --------  -------   ----   -------
1005     Raul       India    Agra    283126
1003     Jon        India    Agra    282008
1006     David      India    Agra    282005
1004     Chetan     India    Noida   201313
1001     Paul       India    Noida   201310
1002     Ajeet      India    Noida   201301