Tutorial Gateway

SQL ORDER BY Clause

by

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 this ORDER BY statement.

SQL ORDER BY 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 an example.

-- SQL Server Order By Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]

OUTPUT
SQL Order By Clause 1

SQL ORDER BY ASC

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 a particular region and your job is to identify the low performing products (Products with No Sales or fewer 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 Single Column in Ascending

In this SQL Asc example, We are going to sort the Data in the Ascending Order using Yearly Income.

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

OUTPUT
SQL Order By Clause 2

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

SQL Order by Ascending Without using ASC

ASC keyword is the default keyword in the SQL Server 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 Ascending Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome]

OUTPUT
SQL Order By Clause 3

SQL Order By String in Ascending Order

In this SQL sort by ascending example, we used a string column called Education to sort the Data. This will sort the data in ascending order (Alphabetical Order) using Education column.

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

OUTPUT

SQL Order By Clause 4

SQL Order By Number in Asc (Numeric Position)

This SQL sort by number example, We are going to sort the Customer table in the Ascending Order using the Numerical Position of a Column Name.

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

The Numerical position of the Occupation Column is 4 so the data will be sorted by this column.

OUTPUT

SQL Order By Clause 5

SQL Order By Ascending using Alias Column

In this SQL sort by ascending example, We are going to sort the records in the Ascending Order using the Alias Column Name.

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

We added the [FirstName] and [LastName] column to create [Full Name] (Which is an Alias Name). Next, we used that 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 Clause 6

SQL Order By Multiple Columns in Ascending Order

In this SQL sort multiple columns 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 Ascending Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] ASC, [Occupation] ASC

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

OUTPUT
SQL Order By Clause 7

SQL ORDER BY Desc

To sort the data Descending order, we have to use the ORDER BY statement followed by DESC keyword.

For example, If you are searching for a 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).

The following are the some of the total number of ways, we can use SQL sort to sort the data in Descending order.

SQL Order By Single Column in Descending Order

In this SQL Desc example, we used the Yearly Income column. It means, Customers data is by this columns in the Descending Order.

-- Sql Server Order By Descending Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC

OUTPUT
SQL Order By Clause 8

From the above screenshot, you can see that Customer table is sorted by the [YearlyIncome] Column in the Descending order.

SQL Order By DESC String Column to Sort in Descending Order

In this SQL sort by string in Descending example, we used the Occupation (varchar column) to sort the data in Descending order. Below code sort the customers in customers table by Occupation in Descending order (Reverse Alphabetical Order)

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

OUTPUT
SQL Order By Clause 9

SQL Order By Number in Descending Order (Numerical Position)

The Desc in Sql Server allows you to sort table using Numeric position of a column. The Numerical position of a Sales Column is 7. So the customer’s data will be sorted by this Sales  column in the Descending order

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

OUTPUT

SQL Order By Clause 10

SQL Order By DESC using Alias Column

This SQL sort Desc example, We are going to use the Alias Column Name to sort the Data in the Descending Order

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

We combined the [FirstName] and [LastName] columns to create [Full Name]. Next, we used that Alias name in the ORDER BY DESC. It means Data will be sorted by the [Full Name] in the Descending Order.

OUTPUT

SQL Order By Clause 11

SQL Order By Ascending and Descending Order

The Sql Server 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 the data is sorted by [Occupation] in the Descending Order

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

OUTPUT

SQL Order By Clause 12

Let us use the DESC keyword first and ASC keyword second. In this example, we are sorting the Customers 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 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC, [Education] ASC

OUTPUT

SQL Order By Clause 13

SQL Order By Case Statement

In this SQL sort case example, we used the Case Statement inside an Order By Clause. This will help you to create your own Order (Custom Order).

Below code will sort the customer data by Occupation column.

  • If the Occupation is Management then it will come first
  • If it is Professional, it will come second etc
-- Sql Server Order By Clause Example

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY (CASE [Occupation] 
				WHEN 'Management' THEN 1
				WHEN 'Professional' THEN 2
				WHEN 'Skilled Manual' THEN 3
				ELSE 4
			END
			)ASC

OUTPUT

SQL Order By Clause 14

SQL Order By Group By Clause

In this SQL sort group by example, we used the Group By Clause along with Order By. This combination of group by and order can help you to sort the Grouped or aggregated data

Below statement will find the sum of yearly Income, and Sales grouped by education and Occupation. Next, Order By Desc statement sort the Group By result based on the Sum of yearly Income in descending order.

-- Sql Server Order By Clause Example
SELECT [Education]
      ,[Occupation]
      ,SUM([YearlyIncome]) AS [Total Income]
      ,SUM([Sales]) AS [Total Sales]
  FROM [Customer]
  GROUP BY Education, Occupation
  ORDER BY SUM([YearlyIncome]) DESC

OUTPUT

SQL Order By Clause 15

SQL Order By Top Clause

In this SQL sort top example, we used the Top Clause along with Order. The following query select the first 8 records order by yearly income in descending order.

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

OUTPUT

SQL Order By Clause 16

Thank You for Visiting Our Blog

Placed Under: SQL