Sorting
Sorting of data returned by the query is done using ORDER BY clause. ORDER BY clause accepts one or more columns and each column can be sorted in ascending order (ASC) or descending order (DESC). By default, data is sorted in ascending order.
Columns can be ordered by either column name, column alias or by column position.
I. Order by column name
A result set can be sorted by column name by using ORDER BY clause followed by the column name followed by the direction in which to sort. The column name must be part of a table specified in the FROM clause. If there exists more than one table in the FROM clause with the same column name, we need to qualify the column name with table name or table alias.
SYNTAX :
SELECT Column1, Column2 …
FROM Table1
ORDER BY [Column Name] [ASC|DESC]
II. Order by column alias
If a column alias is defined in the SELECT clause, the result set can be sorted by column alias by using the alias instead of the column name in the ORDER BY clause. The reason ORDER BY clause allows referencing column alias whereas WHERE, GROUP BY & HAVING clause does not is because ORDER BY comes after SELECT clause in the logical query processing phase.
SYNTAX :
SELECT Column1, Column2 AS [Column Alias]
FROM Table1
ORDER BY [Column Alias] [ASC|DESC]
III. Order by column position
A result set can also be sorted by the relative position of the column in the select clause by using ORDER BY clause followed by the numeric position of the column followed by the direction to sort. The column position is a non-negative integer which begins with 1, where 1 is designated to the first column in the Select clause.
SYNTAX :
SELECT Column1, Column2 …
FROM Table1
ORDER BY [Column Position] [ASC|DESC]
IV. Order by multiple columns
A result set can be sorted by multiple columns by specifying multiple comma separated column names, column alias or column positions in the ORDER BY clause. When multiple columns are specified in the ORDER BY clause, the precedence of sorting is left to right. That is, the result set will first be ordered by the first column, followed by the second column and so on.
SYNTAX :
SELECT Column1, Column2 …
FROM Table1
ORDER BY Column1 [ASC|DESC], Column2 [ASC|DESC]
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID
1 George Cruz CEO 12/8/2008 NULL
2 John Mathew Vice President 4/1/2009 1
3 Ethan William Director I 8/8/2009 2
4 Jacob Logan Director II 12/8/2011 3
5 Nathan Mateo Senior Manager 9/3/2009 4
6 Alice Charles Manager I 12/8/2012 5
7 John Robert Manager II 10/8/2011 6
8 Sophia James Manager III 5/8/2011 7
9 Kevin Chan Product Manager 7/8/2011 5
10 Susan Mortiz Solutions Arthitect 1/3/2012 6
11 Martha Miller Systems Arthitect 12/15/2012 10
Example 1 (Order By column name)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY FirstName
Output :
EmployeeID FirstName LastName
6 Alice Charles
3 Ethan William
1 George Cruz
4 Jacob Logan
2 John Mathew
7 John Robert
9 Kevin Chan
11 Martha Miller
5 Nathan Mateo
8 Sophia James
10 Susan Mortiz
Description :
The above query uses the column name to sort the result set. Since sort direction is not specified in the query, FirstName column is ordered in ascending order, which is the default sort order if a sort direction is not specified.
Example 2 (Order By clause with sort direction)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY FirstName DESC
Output :
EmployeeID FirstName LastName
10 Susan Mortiz
8 Sophia James
5 Nathan Mateo
11 Martha Miller
9 Kevin Chan
2 John Mathew
7 John Robert
4 Jacob Logan
1 George Cruz
3 Ethan William
6 Alice Charles
Description :
The above query orders the result set by FirstName column in descending order.
Example 3 (Order by column alias)
Query :
SELECT EmployeeID, FirstName + ' ' + LastName AS 'Full Name'
FROM Employee
ORDER BY 'Full Name'
Output :
EmployeeID FullName
6 Alice Charles
3 Ethan William
1 George Cruz
4 Jacob Logan
2 John Mathew
7 John Robert
9 Kevin Chan
11 Martha Miller
5 Nathan Mateo
8 Sophia James
10 Susan Mortiz
Description :
The above query orders the concatenated result of FirstName and LastName in ascending order.
Example 4 (Order by column position)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY 2
Output :
EmployeeID FirstName LastName
6 Alice Charles
3 Ethan William
1 George Cruz
4 Jacob Logan
2 John Mathew
7 John Robert
9 Kevin Chan
11 Martha Miller
5 Nathan Mateo
8 Sophia James
10 Susan Mortiz
Description :
In the above query, the EmployeeID is at position 1 followed by FirstName at position 2 followed by LastName at position 3 in the result set. Using this position number, the query orders the FirstName in ascending order.
Example 5 (Ordering multiple columns using column names)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY FirstName, LastName
Output :
EmployeeID FirstName LastName
6 Alice Charles
3 Ethan William
1 George Cruz
4 Jacob Logan
2 John Mathew
7 John Robert
9 Kevin Chan
11 Martha Miller
5 Nathan Mateo
8 Sophia James
10 Susan Mortiz
Description :
In the above query, the dataset is first sorted by FirstName column in ascending order and then by LastName column in ascending order. As you can see in the query result, since there are multiple employees with first name “John”, the query sorts both the employees (employeeID 2 and 7) based on LastName.
Example 6 (Ordering multiple columns using column positions)
Query :
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY 2, 3
Output :
EmployeeID FirstName LastName
6 Alice Charles
3 Ethan William
1 George Cruz
4 Jacob Logan
2 John Mathew
7 John Robert
9 Kevin Chan
11 Martha Miller
5 Nathan Mateo
8 Sophia James
10 Susan Mortiz
Description :
Result of the above query is same as the previous query. Only difference is that, the above query uses column positions to sort the result set instead of column names.