SQL-ORDER BY Clause

<< Previous - SQL Select Distinct

Next - SQL Group By >>

The SQL ORDER BY clause is used to sort the records in the table. This clause is usually sort the records in Ascending or Descending order.

By default this clause sorts the records in ascending order. If you want to sort the records in descending, then use the DESC keyword.

Syntax

SELECT  column-name1, column-name2,...column-nameN
FROM Table_name
[WHERE condition]
ORDER BY column-name1,column-name2..column-nameN
[ASC|DESC] ;

 

EXAMPLE

Following is the demo student table in the collegedb database.

mysql> select * from student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
+--------+---------+------+-----------+
6 rows in set (0.00 sec)

Consider the following query selects all records from student table and would sort the Name column in ascending order by using ORDER BY clause.

mysql> SELECT * FROM student ORDER BY Name;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      3 | Ara     |   19 | Kerala    |
|      1 | Aruna   |   18 | Chennai   |
|      6 | Kanika  |   18 | Chennai   |
|      5 | Kannan  |   20 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      2 | Varun   |   19 | Bangalore |
+--------+---------+------+-----------+
6 rows in set (0.00 sec)

Following query selects all records from student table that would sort the records in descending order by Name column.

mysql> SELECT * FROM student ORDER BY Name DESC;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      2 | Varun   |   19 | Bangalore |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      1 | Aruna   |   18 | Chennai   |
|      3 | Ara     |   19 | Kerala    |
+--------+---------+------+-----------+
6 rows in set (0.00 sec)

Example of ORDER BY More Columns

Following SQL statement selects all records from student table and also sorts the City and Name columns. When you use more than one column in ORDER BY clause, then all records will be ordered by City column, but if some rows have the same City, then that row will sort by Name column.

mysql> SELECT * FROM student ORDER BY City, Name;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      2 | Varun   |   19 | Bangalore |
|      1 | Aruna   |   18 | Chennai   |
|      6 | Kanika  |   18 | Chennai   |
|      3 | Ara     |   19 | Kerala    |
|      5 | Kannan  |   20 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
+--------+---------+------+-----------+
6 rows in set (0.00 sec)

<< Previous - SQL Select Distinct

Next - SQL Group by >>