Sort the result in SQL

PRASHANT SINGH
Last Updated: May 13, 2022

Introduction

To sort the result set in the database, we use the ORDER BY clause. We can sort the results in ascending or descending order as per our requirements.

This command sorts the results/records (of the table) in ascending order by default. We can sort the result in descending order using the DESC clause in its command. We can also use the ASC clause to sort the result in ascending order.

It sorts the results based on the specific column of a table accordingly.

Sort According To Single Column

To sort the result in ascending order or descending order, we can use ASC or DESC clause, respectively and it gives the result by sorting the data of a column in ascending or descending order. Its syntax is discussed below in the syntax section.

Sort According To Multiple Column

For sorting the results according to multiple columns, we can use ASC and DESC clauses.

We have to separate the names of the columns by the “,” operator. Its syntax is explained below.

Syntax

The basic syntax for the ORDER BY command is as follows

ORDER BY Command:

SELECT expressions 

FROM tables 

ORDER by expressions ASC | DESC;

We can use the WHERE clause to sort the result in the table according to some conditions. Its syntax is as follows.

SELECT expressions 

FROM tables 

WHERE condition

ORDER by expressions ASC | DESC;

 

 

Arguments or Parameters

Expressions 

It is the calculation or the columns that we need to retrieve.

Tables 

It is the table from which we want to sort the data.

WHERE conditions

It is an optional clause used when we want to sort our data according to some conditions.

ASC 

It is used to sort the result set in ascending order.

DESC

It is used to sort the result set in descending order.

Example 

Let’s take an example that shows how to sort the result in ascending and descending order one by one. 

Ascending order (single column)

Suppose there is a table named customers in the database, and we want to sort the table’s data in ascending order. 

Given below is the Customer table in a database.

CustomerID

CustomerName

City

Contact

Age

Country

1

Nayan Prakash

Mumbai

72569846XX

28

India

2

Ajay Dixit

Noida

99654852XX

21

India

3

Anjali Singh

Gurgaon

98865424XX

24

India

4

Ayush Mishra

Delhi

75546556XX

31

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

The below SQL statement sorts the result in ascending order by a given column field. The following example would sort the result according to the Age column in ascending order. 

SELECT *

From Customers

ORDER BY Age ASC;

It’s not necessary to use the ASC clause because it is in ascending order by default.

Now, the result table will be as follows.

CustomerID

CustomerName

City

Contact

Age

Country

2

Ajay Dixit

Noida

99654852XX

21

India

3

Anjali Singh

Gurgaon

98865424XX

24

India

1

Nayan Prakash

Mumbai

72569846XX

28

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

4

Ayush Mishra

Delhi

75546556XX

31

India

Thus, all the records associated with the Customers table will be sorted according to the age field in the database.

Descending order(single column)

Given below is the Customers table in some database.

CustomerID

CustomerName

City

Contact

Age

Country

1

Nayan Prakash

Mumbai

72569846XX

28

India

2

Ajay Dixit

Noida

99654852XX

21

India

3

Anjali Singh

Gurgaon

98865424XX

24

India

4

Ayush Mishra

Delhi

75546556XX

31

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

The following SQL statement sorts the result in ascending order by a given column. This example would sort the result according to the Age column in ascending order with customer id greater than two. 

SELECT *

From Customers

WHERE CustomerID > 2

ORDER BY Age ASC;

There will be three records selected whose customer id is greater than two which are following. 

CustomerID

CustomerName

City

Contact

Age

Country

3

Anjali Singh

Gurgaon

98865424XX

24

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

4

Ayush Mishra

Delhi

75546556XX

31

India

Thus, all the records associated with the Customers table will be sorted in descending order according to the age field in the database.

Using both ASC and DESC clauses (Multiple columns )

We can use both ASC and DESC with the ORDER BY clause when sorting the result.

The following example will explain this better.

Given is the Customers table-

CustomerID

CustomerName

City

Contact

Age

Country

1

Nayan Prakash

Mumbai

72569846XX

28

India

2

Ajay Dixit

Noida

99654852XX

21

India

3

Anjali Singh

Gurgaon

98865424XX

24

India

4

Ayush Mishra

Delhi

75546556XX

31

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

6

Ram Singh

Surat

64455515XX

24

India

7

Sheetal Mishra

Gwalior

91465505XX

24

India

Now using SQL statement.,

SELECT *

From Customers

WHERE CustomerID >2

ORDER BY Age ASC, City DESC;

There will be five records of the table are selected that are

CustomerID

CustomerName

City

Contact

Age

Country

6

Ram Singh

Surat

64455515XX

24

India

7

Sheetal Mishra

Gwalior

91465505XX

24

India

3

Anjali Singh

Gurgaon

98865424XX

24

India

5

Nimish Goyal

Kanpur

74123369XX

28

India

4

Ayush Mishra

Delhi

75546556XX

31

India

This example gives the result sorted by Age field in descending order, with secondary sort bu City field in ascending order respectively.

FAQs

  1. What is the SELECT statement?
    The SELECT statement is used to select data from a database. Its syntax is 
SELECT expression From table_name;

Here expression can be column names etc.
If we want to select all fields in the table we can use the following statements.

SELECT * From table_name ;

2. What is the result set?
Since the SELECT statement is used to select the data from the database, The data returned by this SELECT statement is stored in a result table known as a result set.

Key Takeaways

In this blog, we learn how to sort the results in the database using SQL queries. 

We begin with learning how to sort the result in ascending or descending order as per our requirements, the syntax of these statements, and how to use them in queries with various examples. We also learned about sorting the result set by single and multiple column fields.

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 ?
0 upvotes

Comments

No comments yet

Be the first to share what you think