SQL Order By Clause

Read this article to understand the Order by clause along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of Order by clause with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of Order By clause and why we should use it.

Definition

The ORDER BY Clause or Keyword is used to sort the data. Order by clause sort the data in ascending as well as descending order. We can use order by clause with one or more than one columns. Order by can be used with combination of ASC(ascending) as well as DESC(descending). The default order of order by clause is Ascending(ASC).

Reasons to Use

As it’s name suggest we can use the order by clause to sort the data in any order. Below are the some key areas where we need to use order by clause.

1. Show the top 10 highest scoring students.
2. Show the top 3 highest salary employees.
3. Show the recent reason by which employee left the company.
4. Show the latest credit card transactions.
5. Show the top trending videos.

Syntax

SELECT column_name1, column_name2, ...
FROM table_name
ORDER BY column_name1 [ASC|DESC], column_name2 [ASC|DESC], ...;

Examples

Example 1:

Sort all the records from Customers table as per their ages.

customer_id	first_name	last_name	age	country
5	James	Doe	19	INDIA
2	Robert	Luna	22	USA
4	John	Sinha	22	UK
1	John	Doe	31	USA
3	David	Robinson	32	UK

While printing the records, I got one question in mind. What if two records have the same age. Can we sort such records using their names?

Answer is Yes. We can use multiple columns for sorting the data. Few columns we can use for sorting the data using ascending while remaining we can use for descending if we want. Lets sort above records age wise and if having same age then sort as per their names.

Example 2 :

The customer_id 2 and 4 has swapped their positions. As it’s ages are same so these records are again sorted based on their first name.

Can we sort few records with ascending and few with descending?

Yes! Lets understood the below example with SQL. I have updated the age for this example only.

Example 3 :

I hope all above example clears the concept of order by clause and it’s use.

Linking

Order By clause is used in Select statement. We can use Order By after Where condition. Advance level order by having the Case conditions also. You can refer all the interview questions. Please refer all the links for understanding the Order By in depth.

Order By Clause with Latest SQL Interview Questions 2023

Basic Order By SQL Interview questions

1. What is the purpose of the ORDER BY clause?
2. How do you sort results in ascending order using the ORDER BY clause?
3. How do you sort results in descending order using the ORDER BY clause?
4. Can you use the ORDER BY clause with multiple columns? If so, how?
5. What is the default sort order if you don’t specify ASC or DESC in the ORDER BY clause?

Intermediate Order By SQL Interview questions

1. Can you use column aliases in the ORDER BY clause? If so, how?
2. Can you use the ORDER BY clause with aggregate functions like SUM or COUNT? If so, how?
3. What is the difference between the ORDER BY clause and the GROUP BY clause?
4. Can you use the ORDER BY clause with subqueries? If so, how?

Advanced Order By SQL Interview questions

1. How do you sort results by a column that contains NULL values?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que . How do you sort results by a column that contains NULL values?

ANS : What do you think about the answers. There are a multiple ways to solve this question. The normal way to sort the data either show first null values or show null values at last. For showing the null values at first, always sort the data in ascending order and vice versa.

select * from customers
order by age desc ;

Above query will move the records at last where age is null. Second way is to apply the where conditions for null records and sort the data.

select * from customers
where age is not null
order by age desc ;

Sometimes we want to sort the data in descending order but want to move all null values at top then we can use coalesce function to replace the values for sorting only. Lets check the below syntax.

select * from customers
order by coalesce(age,'N/A') desc;

Above query will move all null age records at top and then it will show the records in age descending order .

Quiz

Click on this link to solve few MCQ questions on Order By clause. You can practice all examples here or here.

I hope you enjoyed this Order By clause session. Happy offline learning… Lets meet in next session.