SQL ORDER BY Clause

The SQL ORDER BY Clause is used to sort the data in either Ascending order or Descending order. In SQL Tables, By default data will not be inserted in any order unless you have any indexes. So, If you want to retrieve the data in any particular order then you have to sort the data using ORDER BY statement.

SQL ORDER BY Clause Syntax

The basic syntax of the SQL server Order By Clause is as shown below:

-- SQL SERVER ORDER BY SYNTAX
SELECT [Column_Names]
FROM [Table_Name]
ORDER BY {Column, Numerical Position, Alias Columns}[ASC | DESC]

From the above syntax you can observe that, We can use the column Names or Numerical Position of the Column Names or Alias Column Names in the Order By statement.

We are going to use the below shown data to explain the SQL ORDER BY statement with example.

SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]

OUTPUT

SQL ORDER BY Ascending

To sort the data in Ascending order we have to use the ORDER BY statement followed by ASC keyword. In SQL, Following are the number of ways we can sort the data in Ascending order.

For example, If you are Sales Manager for particular region and your job is to identify the low performing products (Products with No Sales or less sales) then you can write the query as Select Product Name from Products Table Order By Sales in Ascending Order. By seeing the result you can understand that x is not performing well and y has no sales at all, Using this data you can try different strategies to improve sales.

SQL Order By ASC – Sorting in Ascending Order using Column

In this example, We are going to sort the Data in the Ascending Order using any of the Column Name.

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
ORDER BY [YearlyIncome] ASC

OUTPUT

From the above screenshot you can observe that, Data is sorted by the [YearlyIncome] Column in the Ascending order.

SQL Order by ASC – Sort in Ascending Order Without using ASC

ASC keyword is the default keyword in the SQL Order By statement that’s why it is optional to use the ASC keyword in Order by Clause. In this example, We are going to use the above example without ASC Keyword.

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
ORDER BY [YearlyIncome]

OUTPUT

SQL Order By Clause – Sorting in Ascending Order Using Numerical Position

In this example, We are going to sort the Data in the Ascending Order using the Numerical Position of a Column Name.

-- Sql Server Order By Clause Example
SELECT [FirstName] -- Numerical Position = 1 
      ,[LastName] -- Numerical Position = 2 
      ,[YearlyIncome] -- Numerical Position = 3 
      ,[Education] -- Numerical Position = 4 
      ,[Occupation] -- Numerical Position = 5 
FROM [Customer]
ORDER BY 3 ASC

The Numerical position of a [YearlyIncome] Column is 3 so the data will be sorted by this column.

OUTPUT

SQL Order By ASC – Sorting in Ascending Order Using Alias Column

In this example, We are going to sort the Data in the Ascending Order using the Alias Column Name.

-- Sql Server Order By Clause Example
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [SQL Server Tutorials].[dbo].[Customer]
ORDER BY [Full Name] ASC

We added the [FirstName] and [LastName] column to create [Full Name] (Which is Alias Name) and then used the Alias name in the ORDER BY Clause. It means Data will be sorted by the [Full Name] in the Ascending Order.

OUTPUT

SQL Order By ASC – Sorting Multiple Columns in Ascending Order

In this example we are sorting the Data by [YearlyIncome] in the Ascending Order and then we are sorting the data by [Occupation] Column in the Ascending Order

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [SQL Server Tutorials].[dbo].[Customer]
ORDER BY [YearlyIncome] ASC, [Occupation] ASC

--OR you can Simply Write 
ORDER BY [YearlyIncome], [Occupation]

OUTPUT

SQL ORDER BY Descending

To sort the data in Descending order we have to use the ORDER BY statement followed by DESC keyword. In T-SQL, Following are the number of ways we can sort the data in Descending order.

For example If you are searching for shoe in Amazon, when you type shoe in the search bar then it will display the shoes by Rating. It means Shoes are sorted as per the Rating (Technically, Selecting Shoe from Product table order by Product Rating in descending order).

SQL Order By DESC – Sorting in Descending Order Using Column Name

In this example, We are going to sort the Data in the Descending Order using any of the Column Name.

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
ORDER BY [YearlyIncome] DESC

OUTPUT

From the above screenshot you can observe that, Data is sorted by the [YearlyIncome] Column in the Descending order.

SQL Order By DESC – Sorting in Descending Order Using Numerical Position

The Numerical position of a [YearlyIncome] Column is 3 so the data will be sorted by this column in the Descending order

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
   FROM [Customer]
    ORDER BY 3 DESC

OUTPUT

SQL Order By DESC – Sorting in Descending Order Using Alias Column

In this example, We are going to sort the Data in the Descending Order using the Alias Column Name.

-- Sql Server Order By Clause Example
SELECT [FirstName] + ' ' + [LastName] AS [Full Name]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
  FROM [Customer]
  ORDER BY [Full Name] DESC

We added the [FirstName] and [LastName] column to create [Full Name] (Which is Alias Name) and then used the Alias name in the ORDER BY Clause. It means Data will be sorted by the [Full Name] in the Descending Order.

OUTPUT

SQL Order By DESC – Combining Ascending and Descending Order

SQL Order By clause allows us to combine the ASC and DESC keywords in a single statement. In this example we are sorting the Data by [YearlyIncome] in the Ascending Order and then we are sorting the data by [Occupation] Column in the Descending Order

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
FROM [Customer]
ORDER BY [YearlyIncome] ASC, [Occupation] DESC

OUTPUT

Let us use the DESC keyword first and ASC keyword second. In this example we are sorting the Data by [YearlyIncome] in the Descending Order and then we are sorting the data by [Education] Column in the Ascending Order

-- Sql Server Order By Clause Example
SELECT [FirstName]
      ,[LastName]
      ,[YearlyIncome]
      ,[Education]
      ,[Occupation]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC, [Education] ASC

OUTPUT

Thank You for Visiting Our Blog