SQL Order By Clause 14

SQL ORDER BY Clause

The SQL ORDER BY Clause is helpful to sort the data in either Ascending or Descending. 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 way, then you have to sort the data using this SQL ORDER BY Clause along with Select statement. The syntax of the SQL Order By Clause to sort is

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 utilize the column Names or Numerical Position of the Column Names or Alias Column Names in the statement. We use the below data to explain the SQL ORDER BY Clause.

SQL Order By Clause 1

SQL ORDER BY ASC

To sort the data in Ascending order in SQL Server, we have to use this statement, followed by the ASC keyword. In SQL, the following are the number of ways we can sort the data in ascending.

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 categorize the Sales in Ascending.

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

This SQL Asc example sort the Data in the Ascending using Yearly Income.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] ASC

As you see, the Data is sorted by the [YearlyIncome] Column in the Ascending.

SQL Order By Clause 2

Ascending Without using ASC

The ASC keyword is the default keyword in and the server arrange records in ascending by default. That’s why it is optional to use the ASC keyword. Here, we use above code without ASC Keyword.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome]
SQL Order By Clause 3

SQL Order By String in Ascending

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

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [Education] ASC
SQL Order By Clause 4

SQL Order By Number in Asc (Numeric Position)

This SQL sort by number example sort the Customer table in the Ascending using the Numerical Position of a Column Name.

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 SQL data will be arranged by this Occupation column.

SQL Order By Clause 5

Ascending using Alias Column

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

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. It means Data will be sorted by the [Full Name] in the Ascending.

SQL Order By Clause 6

SQL Order By Multiple Columns in Ascending

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

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] ASC, [Occupation] ASC

--OR you can Simply remove the ASC Keywords
SQL Order By Clause 7

SQL ORDER BY Desc

To sort the Data Descending 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 organized by Product Rating in descending).

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

SQL Order By Single Column in Descending

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

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

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

SQL Order By Clause 8

SQL Order By DESC String Column to Sort in Descending

In this SQL sort by a string in Descending example, we used the Occupation (varchar column) to sort the data in Descending. The below code arrange the customers in customers table by Occupation in Descending (Reverse Alphabetical)

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY Occupation DESC
SQL Order By Clause 9

Number in Descending (Numerical Position)

The Desc in SQL Server allows 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 arranged by this Sales column in the Descending.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
FROM [Customer]
ORDER BY 7 DESC
SQL Order By Clause 10

SQL Order By DESC using Alias Column

This SQL sort Desc example use the Alias Column Name to arrange the Data in the Descending result set.

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. It means Data will be sorted by the [Full Name] in the Descending.

SQL Order By Clause 11

Ascending and Descending in Same Statement

It also 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, and then the Data is sorted by [Occupation] in the Descending.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] ASC, [Occupation] DESC
SQL Order By Clause 12

Let us use the SQL DESC keyword first and ASC keyword second to arrange column in descending and ascending. It sort the Customers by [YearlyIncome] in the Descending, and then we are sorting the data by [Education] Column in the Ascending.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC, [Education] ASC
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 (Custom one).

The below code will arrange the customer data by Occupation column. If the Occupation is Management, then it will come first. And 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
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 this one can help you to arrange the Grouped or aggregated data.

It finds the sum of yearly Income, and Sales grouped by education and Occupation. The next statement returns the Group By result based on the Sum of yearly Income in descending.

SELECT [Education]
      ,[Occupation]
      ,SUM([YearlyIncome]) AS [Total Income]
      ,SUM([Sales]) AS [Total Sales]
  FROM [Customer]
  GROUP BY Education, Occupation
  ORDER BY SUM([YearlyIncome]) DESC
SQL Order By Clause 15

SQL Order By Top Clause

In this SQL sort top example, we used the Top Clause. It select the first 8 records organized by yearly income in descending.

SELECT TOP 8 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  ORDER BY [YearlyIncome] DESC
SQL Order By Clause 16

Comments are closed.