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 SQL ORDER BY Clause along with Select statement. The basic syntax of the Order By Clause to sort 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 Clause 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 in SQL Server, we have to use the ORDER BY statement, followed by the ASC keyword. In SQL, the following are the number of ways we can sort the data in ascending order.

For example, If you are a Sales Manager for a particular region and your job is to identify the low performing products (Products with No Sales or fewer sales). 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, management people can try different strategies to improve their company 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.

Order by Ascending Without using ASC

The ASC keyword is the default keyword in the Order By clause. That’s why it is optional to use the ASC keyword in Order by Clause. In this example, We are going to use the example mentioned above 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. It will sort the data in ascending order (Alphabetical Order) using the 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 Occupation column.

OUTPUT

SQL Order By Clause 5

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 in SQL Server, we have to use the ORDER BY statement, followed by the DESC keyword.

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

The following are some of the total numbers of ways we can use SQL sort to sort the data in descending.

SQL Order By Single Column in Descending Order

In this SQL Desc example, we used the Yearly Income column. It means This column in the Descending Order sorts customer table data.

-- 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 a string in Descending example, we used the Occupation (varchar column) to sort the data in Descending order.

Below Order by clause 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 the table using the 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

-- 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

Order By Ascending and Descending Order

The 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 SQL 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. It will help you to create your own Order (Custom Order).

Below Order by clause code will sort the customer data by Occupation column.

  • If the Occupation is Management, then it will come first
  • If the Occupation is Professional, it will come second, etc
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. This combination of group by and order by can help you to sort the Grouped or aggregated data

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

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 will select the first 8 records order by yearly income in descending order.

SELECT TOP 8 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC

OUTPUT

SQL Order By Clause 16

Placed Under: SQL