Updated March 20, 2023
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];The 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 ASCThe 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 ASCExamples 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 | [email protected] |
| 002 | Smith | Jamie | [email protected] |
| 003 | Jones | Amy | [email protected] |
| 004 | Reynolds | Andy | [email protected] |
| 005 | Thomas | Sally | [email protected] |
| 006 | Brown | Dan | [email protected] |
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 | [email protected] |
| 001 | Donald | Jo | [email protected] |
| 003 | Jones | Amy | [email protected] |
| 004 | Reynolds | Andy | [email protected] |
| 002 | Smith | Jamie | [email protected] |
| 005 | Thomas | Sally | [email protected] |
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 of Employee containing the following data.
| EmployeeID | EmployeeLastName | EmployeeFirstName | EmailID |
| 001 | Donald | Jo | [email protected] |
| 002 | Smith | Jamie | [email protected] |
| 003 | Jones | Amy | [email protected] |
| 004 | Reynolds | Andy | [email protected] |
| 005 | Thomas | Sally | [email protected] |
| 006 | Brown | Dan | [email protected] |
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 | [email protected] |
| 005 | Thomas | Sally | [email protected] |
| 004 | Reynolds | Andy | [email protected] |
| 003 | Jones | Amy | [email protected] |
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 | |
| 001 | Donald | Jo | [email protected] |
| 002 | Smith | Jamie | [email protected] |
| 003 | Jones | Amy | [email protected] |
| 004 | Reynolds | Andy | [email protected] |
| 005 | Thomas | Sally | [email protected] |
| 006 | Brown | Dan | [email protected] |
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
We hope that this EDUCBA information on “ORDER BY Clause in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
