ORDER BY Clause in SQL

Introduction to ORDER BY Clause in SQL

ORDER BY clause in SQL helps us to categorize our data in either ascending or descending order, depending on the columns of our tables. ORDER BY is the keyword used in our query to help us sort through the data. By default, a few databases categorize the results returned by the query in ascending order. To sort the data present in the records in descending order, we utilize the keyword DESC in our query. We also have the keyword ASC to categorize data in ascending order, though mostly we don’t use it due to default settings of the database. 

Parameters of ORDER BY Clause in SQL

• column_name: This indicates the column name that we wish to retrieve based on which the data is to be arranged.
• table_name: This indicates the name of the table from which the records are to be obtained. We need to have at least one table name in this parameter.
• WHERE condition: This is an optional parameter. WHERE clause contains the conditions that we must meet for the data to be chosen.
• ASC: This is the keyword used to sort the column data in ascending order. If no keyword is mentioned, data is sorted in ascending order by default.
• DESC: This is the keyword used to sort the column data in descending order.
• |: This is just an indicator for “OR”, as we are to use either ASC or DESC in our query accordingly for the results.

ORDER BY Clause

Syntax of ORDER BY is:

SELECT column_name(s)
FROM table_name(s)
[WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

Syntax for sorting data according to a single column

To sort data of the table based on a single column in either ascending or descending order, we can either utilize the ASC or DESC keywords. In our example, we will be sorting data in ascending order thereby using the keyword ASC.

Syntax

SELECT * FROM table_name ORDER BY column_name ASC

The syntax for sorting data according to several columns

To sort data of the table based on several columns in either ascending or descending order, we can either utilize the ASC or DESC keywords. To mention the several columns according to which we will be sorting data, we need to mention the names of the columns separated by the comma (,) operator. In our example, we will be sorting data in ascending order thereby using the keyword ASC.

Syntax

SELECT * FROM table_name ORDER BY column1 ASC, column2 ASC

Examples of ORDER BY Clause

Let us look at various examples to understand the ORDER BY Clause better.

1. Example to sort the results in an ascending Order

To categorize the results in ascending order, we can use the ASC keyword. If no keyword, either ASC or DESC has been provided, then the default sorting order is ascending order. Let us understand this with the help of an example. We have an Employee table.

EmployeeID	EmployeeLastName	EmployeeFirstName	EmailID
001	Donald	Jo	jo@gmail.com
002	Smith	Jamie	jamie@gmail.com
003	Jones	Amy	amy@gmail.com
004	Reynolds	Andy	andy@gmail.com
005	Thomas	Sally	sally@gmail.com
006	Brown	Dan	dan@gmail.com

When we try to arrange the results in ascending order of the Employee’s last name, we can use the following statement, and the result that follows is displayed in the table after.

SELECT *
FROM Employee
ORDER BY EmployeeLastName;

Result

EmployeeID	EmployeeLastName	EmployeeFirstName	EmailID
006	Brown	Dan	dan@gmail.com
001	Donald	Jo	jo@gmail.com
003	Jones	Amy	amy@gmail.com
004	Reynolds	Andy	andy@gmail.com
002	Smith	Jamie	jamie@gmail.com
005	Thomas	Sally	sally@gmail.com

This example gives back all the records from the Employee table sorted in ascending order according to the EmployeeLastName field. We can also use the keyword ASC as follows, to get the same result.

SELECT EmployeeID, EmployeeLastName
FROM Employee
WHERE EmployeeID <> '003'
ORDER BY 1 DESC;

2. Example to sort the results in a descending order

We use the keyword DESC when we want to sort our data in descending order, in our ORDER BY clause. Let us understand this with the help of an example. We have the same table Employee containing the following data.

EmployeeID	EmployeeLastName	EmployeeFirstName	EmailID
001	Donald	Jo	jo@gmail.com
002	Smith	Jamie	jamie@gmail.com
003	Jones	Amy	amy@gmail.com
004	Reynolds	Andy	andy@gmail.com
005	Thomas	Sally	sally@gmail.com
006	Brown	Dan	dan@gmail.com

We need only Employees whose EmployeeID is greater than 2 and we need our data sorted in descending order. We use the following SQL statement for the same and get the result set table with only 4 records.

SELECT *
FROM Employee
WHERE EmployeeID > 002
ORDER BY EmployeeID DESC;

Result:

EmployeeID	EmployeeLastName	EmployeeFirstName	Email ID
006	Brown	Dan	dan@gmail.com
005	Thomas	Sally	sally@gmail.com
004	Reynolds	Andy	andy@gmail.com
003	Jones	Amy	amy@gmail.com

3. Example to sort the results by their relative position

We can also arrange our data by the relative position of the columns, where 1 represents the first field, 2 represents the second field, 3 represents the third field and so on. Let us try to arrange data in our Employee table according to relative positions.

EmployeeID	EmployeeLastName	EmployeeFirstName	email
001	Donald	Jo	jo@gmail.com
002	Smith	Jamie	jamie@gmail.com
003	Jones	Amy	amy@gmail.com
004	Reynolds	Andy	andy@gmail.com
005	Thomas	Sally	sally@gmail.com
006	Brown	Dan	dan@gmail.com

By using the SQL statement as follows, we can arrange the data in descending order of EmployeeID. We have also asserted that we need only two columns from the table, namely EmployeeID and EmployeeLastName, along with using the WHERE clause to mention that we do not want any row containing the EmployeeID 003.

SELECT EmployeeID, EmployeeLastName
FROM Employee
WHERE EmployeeID <> '003'
ORDER BY 1 DESC;

Result

EmployeeID	EmployeeLastName
006	Brown
005	Thomas
004	Reynolds
002	Smith
001	Donald

Since the column at position 1 is EmployeeID, the result set gets sorted according to EmployeeID.

Conclusion

In SQL, the SELECT statement does not return data in any specific order. To guarantee a particular order, we make use of the ORDER BY clause. ORDER BY sorts on the basis of one or more columns. Records are returned in either ascending or descending order. If ASC or DESC keyword hasn’t been provided, then the results will be categorized in ascending order.

Recommended Article

This has been a guide to the ORDER BY Clause in SQL. Here we discuss the parameters and different examples of ORDER BY clause along with syntax. You can also go through our other suggested articles to learn more –

1. Advantages of NoSQL
2. SQL Management Tools
3. T-SQL String Functions
4. PostgreSQL Data Types