SQL Order By Explained [Practical Examples]

Overview of SQL Order By

SQL offers the user to have some control over the order in which resulting data of a relation or table are displayed with the help of SQL Order By clause

The Order by clause is used to manage the order of resulting records to appear in sorted order either in ascending or in descending order of order by expression, by default order by clause returning in ascending order of expression or column name given with order by clause. To specify the sort order, we may specify DESC for descending order or ASC for ascending order

The Order by clause does not affect the ordering of the records in the source table or does not change the physical structure of the table. It is just a logical re-structuring of physical data. Furthermore, orders can be performed on multiple attributes of the table separated by a comma

 

SQL Order By Clause Syntax

ORDER BY order_by_expression
[COLLATE collation_name]
[ ASC | DESC ]

Here,

• order_by_expression: it specifies column name, column alias, or expression on which to sort the query result set, a nonnegative integer representing the position of the column in the select list can also be specified as the order_by_expression
• [ASC | DESC ]: To perform sorting in ascending order from the lowest value to highest value keyword ASC is applied after the order_by_expression, if sorting is performed in descending order the keyword DESC given after order_by_expression

 

Key Elements of SQL Order By clause

• Multiple column names can also be specified in order_by_expression but each column name must be unique.
• The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. That is, the result set is sorted by the first column and then that ordered list is sorted by the second column, and so on
• The column names referenced in the ORDER BY clause must correspond to either a column or column alias in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.
• If the ORDER BY clause references a column alias from the select list, the column alias must be used standalone, and not as a part of some expression in the ORDER BY clause
• There is no limit to the number of columns in the ORDER BY clause; however, the total size of the columns specified in an ORDER BY clause cannot exceed 8,060 bytes
• If a table name is aliased in the FROM clause, only the alias name can be used to qualify its columns in the ORDER BY clause.
• Column names and aliases specified in the ORDER BY clause must be defined in the select list if the SELECT statement contains one of the following clauses or operators: UNION operator, EXCEPT operator, INTERSECT operator, SELECT DISTINCT

 

SQL Order By Examples

Consider company database with two table employee and department for practical examples

Employee Table (tblemp)

Department Table (tbldept)

 

SQL Order By with one column

Example 1: Display all details of employee in ascending order of employee name

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id
FROM tblemp
ORDER BY Emp_name

• In the above SQL Select query, SQL Order by clause has been applied on column name emp_name to sort the resulting records in ascending order of employee name so 'adi' employee record will be the first record in output data
• If the keyword ASC has not been specified after the column name of Order By clause, by default resulting record set is display in ascending order

OUTPUT:

 

Example 2:  Display all details of employee in descending order of city name

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id
FROM  tblemp
ORDER BY city DESC

• In the above SQL Order by query, to fetch the records in descending order of city name the DESC keyword has been specified after the column name
• In the resulting records starts with alphabet V, the rows which have a 'Vadodara' as the city name will display in the top
• If more than one row has the same value for order by expression column name then the records will be displayed as per the order of records of the table

OUTPUT:

 

SQL Order By with multiple columns

Example 3: Display all employee details in the increment order of salary and city

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id
FROM tblemp
ORDER BY Salary, city

• In the previous SQL select statement query, SQL Order By clause is applied on two columns salary and city
• The resulting records set data will be first sorted on Salary and then sorted on the city, it means if the salary value is the same for records then the records will be sorted as per city name

OUTPUT:

 

SQL Order By clause with Joins

Example 4: display employee id, name, city, and department name in sorted order of department name

SELECT tblemp.Emp_id, tblemp.Emp_name, tblemp.city, tbldept.Dept_name
FROM tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id
ORDER BY tbldept.Dept_name ASC

• In the above SQL Order by query, to retrieve records from two tables, INNER JOIN is used to connect two tables ON condition of common column matching values
• To display records in ascending order of department name, SQL ORDER BY clause is specified with column name Dept_name

OUTPUT:

Example 5: Display employee name, department name, city, and salary details in sorted order of salary and city name

SELECT tblemp.Emp_name, tbldept.Dept_name, tblemp.city, tblemp.Salary
FROM  tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id
ORDER BY tblemp.Salary, tbldept.Dept_name

• In the above SQL Select query with Order By, SQL Inner join is used to join two tables employee and department
• SQL Order BY clause is applied on two columns Salary and Dept_name, sorting is performed on salary column first and then according to salary sorted value department name sorting is performed

OUTPUT:

 

SQL Order By with Where clause

Example 6: Display all details of employees whose salary is greater than 30000 in descending order of salary value

SELECT Emp_id, Emp_name, street, city, Emp_contact, Salary, Dept_id
FROM  tblemp
WHERE (Salary > 30000)
ORDER BY Salary DESC

• In the previous SQL Select statement query, where an Order By clause is specified to sort the resulting employee details in decreasing order of Salary whose salary is greater than 30000
• DESC keyword is specified to perform sorting in descending order

OUTPUT:

 

Column position number in SQL Order By clause example

We can also use the SQL ORDER BY clause to sort by the relative position of columns in the result set like first column name in resulting recordset is having position number as 1 and so on

Example 7: Display all details of employee with department name in descending order of department name

SELECT tblemp.Emp_name, tbldept.Dept_name, tblemp.city, tblemp.Emp_contact, tblemp.Salary
FROM tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id
ORDER BY 2 DESC

• In the above SQL query, to retrieved records from two tables, SQL inner join is used to join two tables employee and department
• column position number 2 has been specified with SQL Order By clause which indicates to the second position column in SQL select statement which is the department name

OUTPUT:

 

Specifying column alias With SQL Order By

Example 8: To display employee name, department name, employee city, employee contact number, and Salary in sorting order of employee name and department name

SELECT tblemp.Emp_name AS name, tbldept.Dept_name AS dname, tblemp.city, tblemp.Emp_contact, tblemp.Salary
FROM tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id
ORDER BY name, dname

• In the above SQL Select statement, alias name has been given to two column names, emp_name as name and dept_name as dname
• Column alias name has been specified with SQL Order By clause as the sorting parameter
• To join two tables in the resulting dataset SQL Inner join is used with a common column name

OUTPUT:

 

SQL Order by Clause with Group by to Specify an expression as the sort column

Example 9: to display department wise minimum paid salary in the sorting order of minimum paid salary of each department

SELECT tbldept.Dept_name AS dname, MIN(tblemp.Salary) AS 'Minimum Salary'
FROM tblemp INNER JOIN tbldept ON tblemp.Dept_id = tbldept.Dept_id
GROUP BY tbldept.Dept_name
ORDER BY MIN(tblemp.Salary) 

• In the above SQL select query, aggregate function minimum is applied in Salary column to find department wise minimum salary, to make a group of each department Group By clause is used with the department name column
• Expression MIN (tblemp.Salary) has been specifying with SQL Order BY clause to sort resulting record set in ascending order of minimum Salary of each department

 

Summary

In this Article of SQL Order By, the use of SQL order By clause with its Keynote points, syntax and some key points has been explained, the practical examples on SQL Order by with one column, multiple columns, with ASC and DESC keywords, has been given, also SQL Order By clause with where condition, Group By clause, joins. Specifying column number and alias has also been explained with examples

 

References

SQL Joins
SQL INNER JOIN
SQL GROUP BY Statement

 

Further Reading

SELECT - ORDER BY Clause