Order by in SQL

The amount of data produced each day is increasing day by day, and it’s mind-boggling to see a large amount of data getting generated each day. IN this scenario, it’s significant to sort the data in the database. In SQL, the developers make use of the ORDER BY clause to sort the data in the database. In this tutorial, we will check out the different ways to use the clauses to sort the data.

Here is a quick synopsis of what are the topics we are going to discuss in this tutorial.

• ORDER BY in SQL | An overview
• ORDER BY clause on One column
• ORDER BY clause on One column in Ascending order
• ORDER BY Desc
• ORDER BY on different columns
• Order by the result set by an expression

ORDER BY clause

The ORDER BY clause is used for arranging the results in both ascending or descending order. The set of results are arranged in ascending order by default. If you need to sort them in the descending order, then you can make use of the DESC keyword to do so. Additionally, you will be able to sort in different columns in a table too.

Let’s check out the ORDER BY clause in SQL Syntax below.

SELECT Column1, Column2, Column3, ...ColumnN
FROM TableName
ORDER BY Column1, Column2, Column3, ….. ASC|DESC;

Let me explain this with a few examples.

I have listed a table which contains the Operations in the ORDER BY clause. You can make use of the CREATE table statement to create tables. We have seen how to create a table in detailed in previous topics.

EmployeeID	First name	City	Phone number
1	Vivaan	Chennai	9364548434
2	Sajin	Bangalore	9346364646
3	Shefin	Delhi	9426746734
4	Jancy	Tirunelveli	9346374634
5	Deena	Mumbai	9763434676
6	Poornesh	Delhi	9736476344

With the above table, let’s see each of the ORDER BY clause in depth below.

ORDER BY clause on One column

If you need to arrange the data to a particular column, then you need to mention the column name after the use of the ORDER BY clause in the SQL. Let’s check out an example.

Let’s say; you are going to write a query to choose the employees from the “Employees” table that is sorted by the particular “City” column.

Here is the query of sorting the employee based on the City column.

Select * FROM Employees
ORDER BY City;

When you use the above command and execute them, the output will look like

EmployeeID	First name	Phone number	City
2	Sajin	9346364646	Bangalore
1	Vivaan	9364548434	Chennai
3	Shefin	9426746734	Delhi
6	Poornesh	9736476344	Delhi
5	Deena	9763434676	Mumbai
4	Jancy	9346374634	Tirunelveli

ORDER BY clause on One column in Ascending order

If you need to arrange the data to a particular column, then you need to mention the column name after the use of the ORDER BY clause in the SQL. Let’s check out an example.

Let’s say; you are going to write a query to choose the employees from the “Employees” table that is sorted by the particular “First name” column and the city column in the ascending order.

Here is the query of sorting the employee based on the City column.

Select * FROM Employees
ORDER BY Firstname;

When you use the above command and execute them, the output will look like

EmployeeID	First name	City	Phone number
5	Deena	Mumbai	9763434676
4	Jancy	Tirunelveli	9346374634
6	Poornesh	Delhi	9736476344
2	Sajin	Bangalore	9346364646
3	Shefin	Delhi	9426746734
1	Vivaan	Chennai	9364548434

ORDER BY Desc

Now, if you need to arrange data in reverse, i.e., descending order, in this case, you need to make use of the keyword DESC after the usage of the ORDER BY clause in SQL.

Let’s check out this with an example.

Let’s say; you need to build a query to choose all employees from the “Employees” table, sorted by the city column in the descending order. Then the syntax of this will be

SELECT * FROM Employees
ORDER BY City DESC;

The output on executing the above query will be

EmployeeID	First name	Phone number	City
4	Jancy	9346374634	Tirunelveli
5	Deena	9763434676	Mumbai
6	Poornesh	9736476344	Delhi
3	Shefin	9426746734	Delhi
1	Vivaan	9364548434	Chennai
2	Sajin	9346364646	Bangalore

ORDER BY on different columns

If you need to arrange the data based on several columns, in this case, you have to specify column names after the usage of the ORDER BY clause in SQL.

Let’s check out this with an example.

Let’s say; you are going to write a query to choose all employees from the “Employees” table that is sorted by two columns, namely “City” and “Firstname” columns. The syntax for the example is

SELECT * FROM Employees
ORDER BY City, FirstName;

When executing the above query, the data is sorted based on the city. In case, there are two same cities; then it will consider the names and then order by Firstname. Let’s check out the output after executing the above query.

EmployeeID	First name	Phone number	City
2	Sajin	9346364646	Bangalore
1	Vivaan	9364548434	Chennai
6	Poornesh	9736476344	Delhi
3	Shefin	9426746734	Delhi
5	Deena	9763434676	Mumbai
4	Jancy	9346374634	Tirunelveli

You can also sort the employees depending on, sorted by descending order for “Firstname,” and ascending order for “City”. Here is the syntax for doing so.

SELECT * FROM Employees
ORDER BY City ASC, Firstname DESC;

The output received when executing the above query will look like.

EmployeeID	First name	Phone number	City
2	Sajin	9346364646	Bangalore
1	Vivaan	9364548434	Chennai
3	Shefin	9426746734	Delhi
6	Poornesh	9736476344	Delhi
5	Deena	9763434676	Mumbai
4	Jancy	9346374634	Tirunelveli

Order by the result set by an expression:

In this case, we can make use of the LEN() function to return the number of string characters. Let’s check an example.

Let’s say; you are going to create a query for the employee names to be listed based on the number of strings. In case if there are the same number of strings in a name, then it will be arranged based on the alphabetic order. Here is the command for it.

Select * FROM Employees
Order by LEN(first name) ASC;

The output when executing the query will look like.

EmployeeID	First name	City	Phone number
5	Deena	Mumbai	9763434676
4	Jancy	Tirunelveli	9346374634
2	Sajin	Bangalore	9346364646
3	Shefin	Delhi	9426746734
1	Vivaan	Chennai	9364548434
6	Poornesh	Delhi	9736476344

I hope the above tutorial on SQL ORDER BY clause helped you to understand the concept in-depth. If you have any queries in the ORDER BY in SQL, let us know through the comment section below.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL