ORDER BY Clause

Amisha Purswani
Last Updated: May 13, 2022

Introduction

We often need the records in the table to be sorted in some order. If the records are arranged in increasing order of some column, then it is called ascending order, and if the records are arranged in decreasing order of some column, then it is called descending order. For getting the sorted records in the table, we use the ORDER BY command. By default, the ORDER BY keyword sorts the records in ascending order.

Ordering according to only one column:

Syntax: 

SELECT col1,col2,..coln

FROM table_name 

[WHERE condition] 

[ORDER BY col1, col2, .. coln] [ASC | DESC];

Here

SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`

(column_1,column_2,…) specifies the columns to select from.

Here ASC is for ascending order display, and DESC is for descending order display.

Example

Consider the following student’s records table. What will be the SQL query command if we wish to display the table by ascending order (increasing order) of roll no.?

Roll_NoMarksNameAddressCity
387AAAM.G. roadPune
187DDDChandani chowkDelhi
263GGGViman NagarMumbai
455HHHKuber NagarAhmedabad

The query for the above question will be as follows:

SELECT * 

FROM students_details

ORDER BY Roll_No ASC;

The above query will result in the following output.

Roll_NoMarksNameAddressCity
187DDDChandani chowkDelhi
263GGGViman NagarMumbai
387AAAM.G. roadPune
455HHHKuber NagarAhmedabad

In the same example, if we want to sort in descending order, we have to use DESC in place of ASC.

SELECT * 

FROM students_details

ORDER BY Roll_No DESC;

The above query will result in the following output. 

Roll_No

Marks

Name

Address

City

4

55

HHH

Kuber Nagar

Ahmedabad

3

87

AAA

M.G. road

Pune

2

63

GGG

Viman Nagar

Mumbai

1

87

DDD

Chandani chowk

Delhi

Ordering according to multiple columns:

We can also order from the table according to multiple columns also by using the (,) operator in the syntax as follows:

Syntax:

SELECT col1,col2,..coln

FROM table_name 

[WHERE condition] 

ORDER BY col1 ASC|DESC, col2 ASC|DESC;

Here

SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`

(column_1,column_2,…) specifies the columns to select from.

Example

Consider the same student’s records table. What will be the command if we wish to display the table by descending order of marks and by ascending order of roll no. as well?

SELECT * 

FROM Student_details

ORDER BY Marks DESC, ROLL_NO ASC;

The above query will result in:

Roll_No

Marks

Name

Address

City

1

87

DDD

Chandani chowk

Delhi

3

87

AAA

M.G. road

Pune

2

63

GGG

Viman Nagar

Mumbai

4

55

HHH

Kuber Nagar

Ahmedabad

FAQs

1. What is the syntax if we want to retrieve data in a specific order from the table?

SELECT col1,col2,..coln

FROM table_name 

[WHERE condition] 

[ORDER BY col1, col2, .. coln] [ASC | DESC];

Here

SELECT col1,...coln FROM `table_name` is the command that tells the MySQL server to select the col1,..coln from `table_name.`

(column_1,column_2,…) specifies the columns to select from.

Here ASC is for ascending order display, and DESC is for descending order display.

2. What is the purpose of the ORDER BY clause?

For getting the sorted records in the table, we use the ORDER BY command. The ORDER BY keyword sorts the records in ascending order by default.

3. Can we use the where clause after ORDER BY?

Yes, we can use the WHERE clause with or without the ORDER BY statement. We can filter records by finite values, comparison values, or with sub-SELECT statements. The WHERE clause gives you several options when filtering data.

4. What is the difference between GROUP BY and ORDER BY?

Group by clause is used to group the rows that have the same value, whereas Order by clause sort the result-set either in ascending or in descending order.

Key Takeaways

In this blog, we learned about the ORDER BY clause in SQL queries. 

We begin with learning the syntax of these statements and how to use them in queries with various examples. 

Visit here to learn more about different topics related to database management systems. Also, try CodeStudio to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think