SQL - ORDER BY Clause

---

The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. This clause can sort data by a single column or by multiple columns. Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting by state, city, and then by the person's name.

ORDER BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses, if present in the query.

Note −

• Some databases sort the query results in an ascending order by default.
• To sort the data in ascending order, we use the keyword ASC.
• To sort the data in descending order, we use the keyword DESC.

Syntax

The basic syntax of the ORDER BY clause is as follows −

SELECT column-list 
FROM table_name 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Where, column-list are the columns we want to retrieve from the table_name, and ASC or DESC specifies whether the sorting should be in ascending or descending order, respectively.

Note − You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column, should be in the column-list.

ORDER BY with ASC

We can use the ORDER BY clause with the ASC keyword to sort the result set of a query in ascending order based on one or more columns. When using the ORDER BY clause, if you do not explicitly specify the sort order, ASC is used by default.

Example

Assume we have created a table with name CUSTOMERS in SQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

If we verify the contents of the CUSTOMERS table using the SELECT statement, we can observe the inserted records as shown below −

SELECT * from CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

In the following query we are trying to sort the result in an ascending order by the name and the salary of the customers −

SELECT * FROM CUSTOMERS ORDER BY NAME ASC

Output

This would produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

ORDER BY with DESC

We can also use the ORDER BY clause with the DESC keyword to sort the result set of a query in descending order based on one or more columns.

Example

The following query sorts the result in the descending order by the name of the customers −

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

Output

This would produce the result as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

ORDER BY Multiple Columns

We can use the ORDER BY clause to sort the result set of a query by multiple (more than one) columns. When sorting by multiple columns, the sorting is done in the order that is specified in the ORDER BY clause. In other words, the table will be sorted first by sorting the first column specified, then the second column, and so on.

Example

In the following query, we are trying to retrieve all records from the CUSTOMERS table and sorts them first by their address in ascending order, and then by their salary in descending order −

SELECT * FROM CUSTOMERS ORDER BY AGE ASC, SALARY DESC

Output

Following is the result produced −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |  4500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

ORDER BY with WHERE clause

In SQL, we can use the WHERE clause with the ORDER BY clause to sort only the rows that meet certain specified conditions. This can be useful when we want to sort a subset of the data in a table based on the specified criteria.

Example

Now, we are trying to retrieve all records from the CUSTOMERS table where the age of the customer is 25 and sort them in descending order based on their name −

SELECT * FROM CUSTOMERS
WHERE AGE = 25
ORDER BY NAME DESC;

Output

Following is the output of the above query −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

ORDER BY with TOP clause

We can use the TOP clause with ORDER BY clause to limit the specified number of rows by sorting it in either ascending or descending order.

Syntax

Following is the syntax of using the TOP clause with the ORDER BY clause in SQL −

SELECT TOP N column1, column2, ...
FROM table_name
ORDER BY column_name1 [ASC | DESC], column_name2 [ASC | DESC], ...

Example

In here, we are trying to retrieve the top 4 records from the CUSTOMERS table on the basis of their salary, and sort them in ascending order based on their name −

SELECT TOP 4 SALARY 
FROM CUSTOMERS
ORDER BY NAME;

Output

Following is the output of the above query −

+----------+
| SALARY   |
+----------+
|  6500.00 |
|  8500.00 |
|  2000.00 |
|  1500.00 |
+----------+