How to use ORDER BY Clause in SQL

Sorting the result after extracting the data from the dataset is one of the most basic thing we do to get the pattern or highest/lowest values in a particular field (column). In SQL sorting is done using ORDER BY clause. So in this article we will explore how to use ORDER BY clause in SQL.

In the previous article we have discussed the SQL WHERE clause and different operators (Comparison and Logical) that are use to extract the data from the dataset or database. But if you want to sort the result in Ascending or Descending Order to get the highest or lowest value in any particular field, then how you will do it in SQL.

In SQL you can sort the data using the ORDER BY clause, so let’s explore it.

Table of Content

• SQL ORDER BY
  • Syntax
• Sort According to single column
• ORDER BY with WHERE
• Sort according to multiple column

SQL ORDER BY

SQL ORDER BY clause is used after the WHERE clause (i.e. after filtering the data) to sort the result in either Ascending or Descending order.

ASC: Keyword is used to arrange the result in Ascending Order

DESC: Keyword is used to arrange the result in Descending Order.

Note: 

1. Ascending is a default sort order.
2. ORDER BY clause comes after the WHERE, GROUP BY, and HAVING clause (if present in query)

Syntax:

Must Read: SQL SELECT

Must Read: SQL LIMITS

Now we will use the Employee dataset (that contains Employee ID, Name, Gender, Department, Education, Month of Joining, and CTC) to do some examples to get a clear understanding of how to use the ORDER BY clause in SQL.

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1002	Babloo	M	Engineering	UG	February	23
1003	Chhavi	F	HR	PG	March	15
1004	Dheeraj	M	HR	UG	January	12
1005	Evina	F	Marketing	UG	March	16
1006	Garima	M	Sales	UG	December	10
1007	Fredy	F	Sales	PG	March	10
1008	Hans	M	Admin	PG	November	8
1009	Ivanka	F	Admin	Intermediate	April	7
1010	Jai	M	Peon		December	4

Must Read: Introduction to SQL

Must Read: What is the difference between SQL and MySQL?

Sort According to Single Column

Example 1: Arrange the CTC of the employee in descending order.

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1002	Babloo	M	Engineering	UG	February	23
1005	Evina	F	Marketing	UG	March	16
1003	Chhavi	F	HR	PG	March	15
1004	Dheeraj	M	HR	UG	January	12
1006	Garima	M	Sales	UG	December	10
1007	Fredy	F	Sales	PG	March	10
1008	Hans	M	Admin	PG	November	8
1009	Ivanka	F	Admin	Intermediate	April	7
1010	Jai	M	Peon		December	4

Must Read: How to Create, Insert, and Delete SQL views?

Must Read: Types of Keys in Database

Must Read: Introduction to Normalization

ORDER BY with WHERE

Example 2: Arrange the CTC (in decreasing) of the employees who joined in the month of March.

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1005	Evina	F	Marketing	UG	March	16
1003	Chhavi	F	HR	PG	March	15
1007	Fredy	F	Sales	PG	March	10

Must Read: Subqueries in SQL

Must Read: Aggregate and Scalar Function in SQL

Sort According to Multiple Columns

Example 3: Sort the employee table:

• CTC in Descending Order
• Name in Ascending Order

Query

Output

Employee ID	Name	Gender	Department	Education	Month of Joining	CTC(in Lacs)
1001	Ajay	M	Engineering	Doctoral	January	25
1002	Babloo	M	Engineering	UG	February	23
1005	Evina	F	Marketing	UG	March	16
1006	Fredy	M	Sales	UG	December	10
1007	Garima	F	Sales	PG	March	10

Now if you look closely, you will get that firstly the result is sorted in descending order of CTC and since in the sales department both the employees are getting the same salary (10 lacs). So sorting further the result according to the Name will sort the rows with the same CTC according to Name in  Ascending Order.

Note 

1. In the ORDER BY clause, instead of using the Column name, you can use column number 
2. Using the ORDER BY clause, you can sort the results on a column not defined in the SELECT statement 

Must Read: Difference between SQL and NoSQL

Must Check: SQL Online Course and Certifications

Conclusion

In this article we have discussed how to use ORDER BY clause in SQL with the help of multiple examples.

Hope this article will help you in your Data Science/Data Analyst Journey.