Table of Contents
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 afterorder_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)
Emp_id | Emp_name | street | city | Emp_contact | Salary | Dept_id |
---|---|---|---|---|---|---|
101 | jone | althan | Surat | 1111111 | 20000 | 10001 |
102 | cartin | udhna | Surat | 2222222 | 15000 | 20001 |
103 | krish | ajava | Vadodara | 3333333 | 30000 | 20001 |
104 | dhiru | ramnagar | Vadodara | 8888888 | 36000 | 30001 |
105 | om | althan | Surat | 7777777 | 22000 | 30001 |
106 | adi | vesu | Navsari | 2323232 | 35000 | 10001 |
107 | annant | shivnagar | Navsari | 5555555 | 34000 | 10002 |
108 | yogi | althan | Surat | 8989898 | 25000 | 10002 |
109 | muskan | vesu | Vadodara | 9999999 | 18000 | 10001 |
110 | rudra | kashi | hazira | 1212121 | 31000 | 20001 |
Department Table (tbldept)
Dept_id | Dept_name | Dept_location |
---|---|---|
10001 | Account | Surat |
20001 | Sales | Hazira |
30001 | Finance | Vadodara |
10002 | Marketing | Surat |
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