How to use ORDER BY Clause in SQL

How to use ORDER BY Clause in SQL

3 mins read1.1K Views Comment
clickHere
Vikram
Vikram Singh
Assistant Manager - Content
Updated on Nov 22, 2022 19:36 IST

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.

2022_07_SQL-OrderBy.jpg

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

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:

 
SELECT column_list
FROM table_name
WHERE conditions
ORDER BY column_names [ASC | DESC];
Copy code

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
Employee Dataset

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

 
SELECT *
FROM Employee
ORDER BY CTC DESC;
Copy code

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

 
SELECT *
FROM Employee
WHERE Month of Joining= ‘March’
ORDER BY CTC DESC;
Copy code

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

 
SELECT *
FROM Employee
WHERE Department IN (‘Engineering’, ‘Marketing’, ‘Sales’)
ORDER BY CTC DESC, Name ASC;
Copy code

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.

About the Author
author-image
Vikram Singh
Assistant Manager - Content

Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio

Comments