SQL - ORDER BY Clause

---

---

The SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts the data in ascending order (from lowest to highest), but you can use the DESC keyword to sort the results in descending order (from largest to lowest).

This clause is placed at the end of a query, following the WHERE, HAVING, and GROUP BY clauses, if present. Following are some of the important point we should know about the ORDER BY clause:

• Most databases sort query results in ascending order by default.
• Use the ASC keyword to explicitly specify ascending order.
• Use the DESC keyword to sort in descending order.

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 is list of the columns we want to retrieve; and ASC or DESC specifies the sort order.

We can use more than one column in the ORDER BY clause, but we need to make sure that the column we are using to sort is specified in the column-list.

ORDER BY Clause with ASC (Default)

We can sort the result-set of a query in ascending order (based on one or more columns) using the SQL ORDER BY clause by specifying ASC as the sort order. ASC is the default sort order for this clause, i.e. while using the ORDER BY clause if you do not explicitly specify the sort order, the data will be sorted in ascending order.

Example

Assume we have created a table with name CUSTOMERS in the MySQL 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),
(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, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The table obtained is as shown below:

In the following query, we are sorting the records of the CUSTOMERS table in ascending order based on the column NAME:

SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

This would produce the following result:

ORDER BY Clause with DESC Keyword

To sort the result-set of a query in descending order (based on one or more columns), we need to use the ORDER BY clause by specifying DESC as the sort order.

Example

The following query sorts the records of the CUSTOMER table based on the descending order of the name of the customers:

SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

This would produce the result as follows:

ORDER BY Using Both ASC and DESC

In SQL, the ORDER BY clause allows you to specify different sort directions for different columns within the same query. You can apply ASC (ascending) to one column and DESC (descending) to another.

Example

In the following query, we are sorting the records from the CUSTOMERS table by AGE in ascending order and SALARY in descending order:

SELECT * FROM CUSTOMERS ORDER BY AGE ASC, SALARY DESC;

Following is the result produced:

ORDER BY Clause on Single Column

The ORDER BY clause can also be used to sort the result-set based on a single column. This is the most basic usage of the clause and is commonly used when we want to view data in a simple sorted order.

Example

In the following query, we are retrieving all records from the CUSTOMERS table and sorting them by the AGE column in ascending order (default):

SELECT * FROM CUSTOMERS ORDER BY AGE;

The result will be sorted from the youngest to the oldest customer:

ORDER BY Clause on 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 based on the first column (specified in the query), then the second column, and so on.

Example

In the following query, we are retrieving all records from the CUSTOMERS table and sorting 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;

Following is the result produced:

ORDER BY with WHERE Clause

We can also use the WHERE clause with the ORDER BY clause to sort the rows that meet certain conditions. This can be useful when we want to sort a subset of the data in a table based on the specific criteria.

Example

Now, we are retrieving all records from the CUSTOMERS table where the age of the customer is 25, and sorting them as per the descending order of their names:

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

Following is the output of the above query:

ORDER BY with LIMIT Clause

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

Syntax

Following is the syntax of using the LIMIT clause with the ORDER BY clause in MySQL database:

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

Example

In here, we are retrieving the top 4 records from the CUSTOMERS table based on their salary, and sorting them in ascending order based on their name:

SELECT SALARY FROM CUSTOMERS ORDER BY NAME LIMIT 4;

Output

Following is the output of the above query:

Sorting Results in a Preferred Order

One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order.

Example

To fetch the rows with their own preferred order, the SELECT query used would be as follows:

SELECT * FROM CUSTOMERS ORDER BY (
CASE ADDRESS
   WHEN 'MUMBAI' THEN 1
   WHEN 'DELHI' THEN 2
   WHEN 'HYDERABAD' THEN 3
   WHEN 'AHMEDABAD' THEN 4
   WHEN 'INDORE' THEN 5
   WHEN 'BHOPAL' THEN 6
   WHEN 'KOTA' THEN 7
   ELSE 100 END
);

The above query sorts the CUSTOMERS table based on the custom order defined using the CASE statement. Here, we are sorting the records based on the population of the cities specified in the ADDRESS column.