SQL ORDER BY

SQL ORDER BY

The SQL ORDER BY clause is used to sort the data stored in tables in the database. The sorting can be done in an ascending way, descending way, or based on multiple columns of the table.

Generally, the default sorting order is ascending order.

SELECT expressions
 FROM tables
 WHERE conditions
 ORDER BY expression ASC | DESC; 

The following is the syntax of a general ORDER BY clause:

Example:

Let’s consider the following Customers table.

ID	NAME	AGE	ADDRESS	SALARY
1	Himani gupta	21	Modinagar	22000
2	Shiva tiwari	22	Bhopal	21000
3	Ajeet bhargav	45	Meerut	65000
4	Ritesh yadav	36	Azamgarh	26000
5	Balwant singh	45	Varanasi	36000
6	Mahesh sharma	26	Mathura	22000

 SELECT * 
 FROM Customers
 ORDER BY NAME, SALARY; 

The above query will sort all the records in ascending order based on the NAME and SALARY columns.

Output:

ID	NAME	AGE	ADDRESS	SALARY
3	Ajeet bhargav	45	Meerut	65000
5	Balwant singh	45	Varanasi	36000
1	Himani gupta	21	Modinagar	22000
6	Mahesh sharma	26	Mathura	22000
4	Ritesh yadav	36	Azamgarh	26000
2	Shiva tiwari	22	Bhopal	21000

SELECT *
 FROM CUSTOMERS
 ORDER BY NAME DESC; 

The above query will sort all the records in the table in a descending manner based on the NAME column.

Output:

ID	NAME	AGE	ADDRESS	SALARY
2	Shiva tiwari	22	Bhopal	21000
4	Ritesh yadav	36	Azamgarh	26000
6	Mahesh sharma	26	Mathura	22000
1	Himani gupta	21	Modinagar	22000
5	Balwant singh	45	Varanasi	36000
3	Ajeet bhargav	45	Meerut	65000

There are various topics under SQL ORDER BY clause. Namely, they are:

• SQL ORDER BY ASC – Sorts the data in ascending manner
• SQL ORDER BY DESC – Sorts the data in descending manner
• SQL ORDER BY RANDOM – Sorts random data
• SQL ORDER BY LIMIT – Selects limited data from the database
• SQL ORDER BY Multiple Columns – Sorts the data based on multiple columns.

Let’s discuss each of them in detail.

SQL ORDER BY ASC

SQL ORDER BY ASC clause sorts the data in ascending order based on some columns. Generally, the default sorting order is ascending manner.

Example:

Let’s consider the following Customers table.

ID	NAME	AGE	ADDRESS	SALARY
1	Himani gupta	21	Modinagar	22000
2	Shiva tiwari	22	Bhopal	21000
3	Ajeet bhargav	45	Meerut	65000
4	Ritesh yadav	36	Azamgarh	26000
5	Balwant singh	45	Varanasi	36000
6	Mahesh sharma	26	Mathura	22000

SELECT * 
 FROM Customers
 ORDER BY NAME, SALARY; 

The above query will sort all the records in ascending order based on the NAME and SALARY columns.

Output:

ID	NAME	AGE	ADDRESS	SALARY
3	Ajeet bhargav	45	Meerut	65000
5	Balwant singh	45	Varanasi	36000
1	Himani gupta	21	Modinagar	22000
6	Mahesh sharma	26	Mathura	22000
4	Ritesh yadav	36	Azamgarh	26000
2	Shiva tiwari	22	Bhopal	21000

SQL ORDER BY DESC

SQL ORDER BY DESC clause sorts the data in descending order based on some columns.

Example:

Let’s consider the following Customers table.

ID	NAME	AGE	ADDRESS	SALARY
1	Himani gupta	21	Modinagar	22000
2	Shiva tiwari	22	Bhopal	21000
3	Ajeet bhargav	45	Meerut	65000
4	Ritesh yadav	36	Azamgarh	26000
5	Balwant singh	45	Varanasi	36000
6	Mahesh sharma	26	Mathura	22000

SELECT *
 FROM CUSTOMERS
 ORDER BY NAME DESC; 

The above query will sort all the records in the table in a descending manner based on the NAME column.

Output:

ID	NAME	AGE	ADDRESS	SALARY
2	Shiva tiwari	22	Bhopal	21000
4	Ritesh yadav	36	Azamgarh	26000
6	Mahesh sharma	26	Mathura	22000
1	Himani gupta	21	Modinagar	22000
5	Balwant singh	45	Varanasi	36000
3	Ajeet bhargav	45	Meerut	65000

SQL ORDER BY LIMIT

The SQL ORDER BY LIMIT clause helps in retrieving specific number of records from the database table. It is generally used in situations where there are large number of tuples which satisfy the given condition. It sets an upper limit to the number of records that are being retrieved.

In order to use the LIMIT clause in SQL, one has to use the ROWNUM clause with it.

Note: It should be noted that the LIMIT clause is not supported by all SQL versions.

This clause can be specified using the SQL 2008 OFFSET or FETCH FIRST clauses.

Example:

Let’s consider the following Student table.

RollNo	Name	Grade
12001	Aditya	9
12002	Sahil	6
12003	Hema	8
12004	Robin	9
12005	Sita	7
12006	Anne	10
12007	Yusuf	7
12008	Alex	5

SELECT *
 FROM Student
 LIMIT 5; 

The above query will only return the top 5 students from the Student table.

Output:

12001	Aditya	9
12002	Sahil	6
12003	Hema	8
12004	Robin	9
12005	Sita	7

The LIMIT clause can also be used with the ORDER BY clause.

SELECT *
 FROM Student
 ORDER BY Grade DESC
 LIMIT 3; 

The above query will return the top 3 students having the highest grades in descending order.

Output:

12006	Anne	10
12001	Aditya	9
12004	Robin	9

The LIMIT operator is especially useful in situations where limited data needs to be retrieved without using any conditional statements.

The LIMIT operator can also be used with the OFFSET operator.

LIMIT with OFFSET

It is to be noted that the OFFSET value should be greater than zero, and it cannot be negative. If a negative value is supplied, it will return an error.

Example:

Let’s consider the same Student table.

RollNo	Name	Grade
12001	Aditya	9
12002	Sahil	6
12003	Hema	8
12004	Robin	9
12005	Sita	7
12006	Anne	10
12007	Yusuf	7
12008	Alex	5

SELECT *
FROM Student
LIMIT 5 OFFSET 2
ORDER BY RollNo;

This query will return the top 5 records of the Student table excluding the top 2 entries.

Output:

RollNo	Name	Grade
12003	Hema	8
12004	Robin	9
12005	Sita	7
12006	Anne	10
12007	Yusuf	7

LIMIT ALL

There is another clause similar to LIMIT known as LIMIT ALL. The LIMIT ALL clause will not put any limit and will return all the entries of the table.

Example:

Let’s again consider the same Student table.

RollNo	Name	Grade
12001	Aditya	9
12002	Sahil	6
12003	Hema	8
12004	Robin	9
12005	Sita	7
12006	Anne	10
12007	Yusuf	7
12008	Alex	5

Query:

SELECT *
FROM Student
LIMIT ALL;

RollNo	Name	Grade
12001	Aditya	9
12002	Sahil	6
12003	Hema	8
12004	Robin	9
12005	Sita	7
12006	Anne	10
12007	Yusuf	7
12008	Alex	5

SQL ORDER BY Multiple Columns

The SQL ORDER BY Multiple Columns will return the data entries in the column by sorting them using multiple columns which have been given in the query.

It will sort the data based on the order of the column names which have been supplied after the ORDER BY clause. The name of the columns should be added in the corresponding order in which the user wants the sorting to happen.

Multiple column names can be added by separating them using a comma (,). Also, the ASC or DESC keywords can be used to specify the order of the sorting.

Example:

Let’s consider the following Employee table having four columns namely id, first_name, last_name, and salary.

id	first_name	last_name	salary
1	Lisa	Ulman	3000
2	Ada	Muller	2400
3	Thomas	Green	2400
4	Michael	Muller	3000
5	Mary	Green	2400

Query:

SELECT id,  
 first_name,
 last_name,
 salary
 FROM employee
 ORDER BY salary DESC, last_name; 

Output:

id	first_name	last_name	salary
4	Michael	Muller	3000
1	Lisa	Ulman	3000
3	Thomas	Green	2400
5	Mary	Green	2400
2	Ada	Muller	2400

Hence, first, the output has been sorted by salary in descending order and then by last name in ascending order. It is to be noted that if ASC or DESC is not mentioned, it will, by default, sort the both the columns in ascending order.

Conclusion

So, above are some of the most important and frequently used ORDER BY clauses that are used by developers daily in order to perform specific operations on the database and retrieve out data. These clauses can be used individually or they can also be used together, in a combined way, to carry out database operations.