In this post we will see how sql order by works and what are the different ways for using sql order by. This post shows some basic order by syntax and later we will see some advanced way of using sql order by. From the name "Order by", we can understand that if we want to order the rows of our sql query according to our own desire, we will use sql order by.
SQL Order By: Single Column Ascending order
SELECT*
FROM Employee
orderby EmployeeName In this query, we are selecting all the rows from Employee table and the result set will be displayed in ascending order of EmployeeName column.
By default sql order by orders the rows in ascending order. If we explicitly specify the keyword ASC, then also sql order by will do the same ascending ordering.
SELECT*
FROM Employee
orderby EmployeeName asc SQL Order By: Single Column Descending order It is quite common that we will may to order our rows in descending order, not always in ascending order. We can use the DESC keyword with sql order by to sort the rows in descending order.
SELECT*
FROM Employee
orderby EmployeeName desc
SQL Order By: Ordering by multiple columns Often we may have multiple rows with the same value in a column, but it varies in other columns. In our example, we have the employee working in multiple departments. So we may need to sort first with EmployeeName and then if the EmployeeName is same, then we want the rows to orderd by DepartmentName.
SELECT*
FROM Employee
orderby EmployeeName, DepartmentName
Here we have not specified any order type. So it will order the results in default ascending order for both the columns. If we want to order the rows with multiple columns, we can specify the asc or desc for all the columns separately. For example, to sort the rows first in ascending order of EmployeeName and descending order of DepartmentName, we can use the following order by query.
SELECT*
FROM Employee
orderby EmployeeName, DepartmentName desc One thing to notice while ordering with multiple columns, the order of the column name in order by clause is important. If we specify the DepartmentName first and then EmployeeName, the rows will be sorted first using DepartmentName column and if DepartmentName for multiple rows are same, then the rows will be sorted using EmployeeName column.
SELECT*
FROM Employee
orderby DepartmentName desc, EmployeeName
So, although the order by clause has the same 2 columns and same order type for the 2 queries, the resultset will have different order just because of the ordering of the columns. As before we can specify asc if we want but it is not needed.
SELECT*
FROM Employee
orderby DepartmentName desc, EmployeeName asc
Sql Order by: with null values in order by column
Null values are treated as the lowest possible values. Show some example with null value column, desc and asc.
SQl Order by: ordering with column index
It is a good thing that we can order the rows not mentioning the column names, but mentioning the column index.
SELECT*
FROM Employee
orderby 5 desc, 1 asc
The first column is 1, second column is 2, and so on. In the above example, we will achieve the same results by the following command.
SELECT*
FROM Employee
orderby DepartmentName desc, EmployeeName 0 is not a valid index position.
SQL Order by count:
We can also order our rows according to a calculated column.
select EmployeeName,COUNT(*)as cnt
from Employee
groupby EmployeeName
orderby cnt
Here we have a cnt column which is not available in the table. We have this count column generated in the query and we can sort the rows according to the new calculate column. We can also do this using column index.
select EmployeeName,COUNT(*)as cnt
from Employee
groupby EmployeeName
orderby 2
SQL Order By: Not Allowed in Nested Query or Inner Query
Consider the following sql query where we have a nested query or inner query and we have used sql order by in the nested query.
select*
from Employee
where EmployeeId in
(
select EmployeeId
from Employee
where ManagerId = 1
orderby EmployeeName
) A
We will receive the following error message.
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Why order by is invalid in nested query? Because order by is applied at the end of the final result set retrieved, just to be shown in the particular order. Ordering the rows in inner query do not make much sense as the resulting rows from the inner query is not going to be displayed. These rows will be used to in the outer scope to determine the final rows. So we don't need to have the ordering. But of course, in some cases we may need the ordering. If we need to get the first 10 or top 10 employees according to the salary, then we may need to use order by in the nested query. Because in this case, the resulting rows returned by the inner query depends on the ordering. That's why sql order by is allowed in views, inline functions, derived tables, and subqueries if TOP is used.
select*
from Employee
where EmployeeId in
(
selecttop 10 EmployeeId
from Employee
orderby Salary
)
orderby EmployeeName, DepartmentName
Here the order by in the sub query is allowed because it is deciding the EmployeeId who have the top salary. The outer order by is actually deciding the sort order of the display. Note: When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself. SQL Order by: Ordering by a column that is not in select list The ORDER BY clause can include items that do not appear in the select list.
select EmployeeName, DepartmentName
from Employee
orderby Salary Here, salary is not included in the select list but the rows are sorted using the salary column. But in this, we cannot use column index instead of column name. For example, salary is the 3rd column in Employee table. But as salary is not included in the select list, we cannot write the following query.
select EmployeeName, DepartmentName
from Employee
orderby 3 It will give us the following error. The ORDER BY position number 3 is out of range of the number of items in the select list. So column index is used according to the position we have mentioned in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
selectdistinct EmployeeName, DepartmentName
from Employee
orderby salary This error message will be displayed: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Data Types not allowed in SQL Order by: In sql server, ntext , text, image, or xml columns cannot be used in an ORDER BY clause. Sql order by case when: This is the most advanced use of sql order by. We can order our rows conditionally.
declare @order asvarchar='salary'
select EmployeeName, DepartmentName
from Employee
orderbycase @order when'salary'then salary
when'name'then EmployeeName
end In this query the order by column is decided based on the value of the @order variable. If it contains salary, then it will be sorted by salary. If it contains name then it will be sorted by EmployeeName. We can also specify descending order with order by case.
declare @order asvarchar='salary'
select EmployeeName, DepartmentName
from Employee
orderbycase @order when'salary'then salary
when'name'then EmployeeName
enddesc Now suppose we want to sort the rows in descending order when @order='name', but in ascending order when @order='salary'. Unfortunately we cannot specify the order by beside the column name. We have to use multiple case in order by clause.
declare @order asvarchar = 'name'
select EmployeeName, DepartmentName
from Employee
orderby casewhen @order='salary'then salary endasc,
casewhen @order='name'then EmployeeName enddesc
When @order='salary', the first case will return salary and we have specified asc. Then second case will not return anything in this scenario. The opposite will be true for @order='name'.