Don't let the Lockdown slow you Down - Enroll Now and Get 3 Course at 24,999/- Only. Explore Now!

General

SQL ORDER BY Clause

SQL ORDER BY Clause

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.

EmployeeIDFirst nameCityPhone number
1VivaanChennai9364548434
2SajinBangalore9346364646
3ShefinDelhi9426746734
4JancyTirunelveli9346374634
5DeenaMumbai9763434676
6PoorneshDelhi9736476344

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

EmployeeIDFirst namePhone numberCity
2Sajin9346364646Bangalore
1Vivaan9364548434Chennai
3Shefin9426746734Delhi
6Poornesh9736476344Delhi
5Deena9763434676Mumbai
4Jancy9346374634Tirunelveli

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

EmployeeIDFirst nameCityPhone number
5DeenaMumbai9763434676
4JancyTirunelveli9346374634
6PoorneshDelhi9736476344
2SajinBangalore9346364646
3ShefinDelhi9426746734
1VivaanChennai9364548434

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

EmployeeIDFirst namePhone numberCity
4Jancy9346374634Tirunelveli
5Deena9763434676Mumbai
6Poornesh9736476344Delhi
3Shefin9426746734Delhi
1Vivaan9364548434Chennai
2Sajin9346364646Bangalore

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.

EmployeeIDFirst namePhone numberCity
2Sajin9346364646Bangalore
1Vivaan9364548434Chennai
6Poornesh9736476344Delhi
3Shefin9426746734Delhi
5Deena9763434676Mumbai
4Jancy9346374634Tirunelveli

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.

EmployeeIDFirst namePhone numberCity
2Sajin9346364646Bangalore
1Vivaan9364548434Chennai
3Shefin9426746734Delhi
6Poornesh9736476344Delhi
5Deena9763434676Mumbai
4Jancy9346374634Tirunelveli

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.

EmployeeIDFirst nameCityPhone number
5DeenaMumbai9763434676
4JancyTirunelveli9346374634
2SajinBangalore9346364646
3ShefinDelhi9426746734
1VivaanChennai9364548434
6PoorneshDelhi9736476344

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
Scroll Up
Besant Technologies WhatsApp