Order by

Order by

Order by clause is used to sort the records of a result set in ascending or descending order.

  • It can be applied to more than one column.
  • We use two keywords to sort the records. ASC and DESC. ASC is for sorting in ascending order and DESC is for descending. By default Order by works as ASC. That means, ASC is optional if we want data in ascending order.
  • It does not work for sub queries.
  • We can use both ASC and DESC in the same query for the different columns.
  • Not only by the column name in the query, we can also sort based on column position in the query which is not recommended. If the columns position changes in future, the order by works on other column.
  • SQL Server Executes order by at the end of the query.

Syntax:

SELECT <Column_Name(s)> FROM <Table_Name>
ORDER BY <Column_Name(s)> ASC|DESC

Example 1:

/*——————————————————————–
Simple Order By
———————————————————————-*/
USE [AdventureWorks2012];
GO

SELECT TOP 3 [BusinessEntityID], [JobTitle]
FROM [HumanResources].[Employee]
ORDER BY [BusinessEntityID] DESC
———————————————————————-

The above query returns TOP 3 records by sorting [BusinessEntityID] in descending order.

Result set:

Simple Order By

Example 2:

/*——————————————————————–
Order By on Multiple Columns
———————————————————————-*/
USE [AdventureWorks2012];
GO

SELECT TOP 5 [Gender], [Maritalstatus]
FROM [HumanResources].[Employee]
ORDER BY [Gender] , [Maritalstatus] DESC
———————————————————————-

Since we have not mentioned ASC/DESC for [Gender], first the query will sort the Genders in Ascending order by default and then it will sort the [Maritalstatus] in Descending order.

Result set:

Order by with multiple Columns

Leave a Reply

Your email address will not be published. Required fields are marked *