The 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.

Following are the important points about ORDER BY Clause −

• 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.

In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table in a preferred order.

This preferred order may not sort the records of a table in any standard order (like alphabetical or lexicographical), but they could be sorted based on external condition(s).

For instance, in the CUSTOMERS table containing the details of the customers of an organization, the records can be sorted based on the population of the cities they are from. This need not be alphabetically sorted, instead, we need to define the order manually using the CASE statement.

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.

Note: 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

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;

Output

This would produce the following result −

ORDER BY Clause with DESC

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;

Output

This would produce the result as follows −

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;

Output

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;

Output

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
);

Output

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.