SQL ORDER BY Clause

The SQL ORDER BY Clause is helpful to sort the data in either Ascending or Descending order. In 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 Clause along with the Select statement. The syntax of the SQL Order By Clause to sort is

SELECT [Col_Names]
FROM [Table_Name]
ORDER BY {ColName, 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 Columns or Alias Name in the statement. And also, we can use one or more columns. We use the below data to explain the SQL ORDER BY Clause.

SQL ORDER BY ASC

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

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

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.

Order by Single Column in Asc

The following SQL Order By query sort 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].

SQL Order By Ascending Without using ASC

The ASC keyword is the default keyword and the server arrange records alphabetically because it is the default sort order. That’s why it is optional to use the ASC keyword. In the following sql statement, we used the previous query without ASC Keyword.

Order By String in Ascending

In this example, we used a string column called Education to arrange the Data. It will sort them in Alphabetical order using the Education.

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

Order By Number in Asc (Numeric Position)

The following sql statement example sort the Customer table in the Asc using the Numerical Position of a Column.

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 is fourth column so the SQL will arrange the records by the Occupation.

Ascending order using Alias Column

In this asc sorts example, We are going to arrange the result in the Ascending using the Alias Name.

We added the [FirstName] and [LastName] to create [Full Name] (Which is an Alias Name). Next, we used that Alias name. It means customers sorted by the [Full Name].

SQL Order By Multiple Columns in Ascending

In this order by multiple columns example, we are sorting the customers by [YearlyIncome], and then we are sorting by [Occupation]. Both the columns will be in the Asc Order. It means, we used more than one column to sort in particular 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 Desc

To sort data in the Descending, we have to use the ORDER BY statement, followed by the keyword DESC. 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 in descending.

Order By Single Column in Descending

In this example query, we used the Yearly Income. It means SQL is ordering data in the customer table by yearly income in the Descending or desc.

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

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

Order By String Column to Sort in Descending

In this SQL order by clause sort by a string in Descending or desc example, we used the Occupation (varchar). The below code arranges all the records in the customer’s table by Occupation in Descending or Reverse order.

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

Number in Descending (Numerical Position)

The following SQL Order by query allows sorting the table using the Numeric position of a column name, where the first column is 1. The Numerical position of a Sales is 7. So the customers will be arranged by this Sales in the Descending.

Order By DESC using Alias Column

This example use the Alias Name to arrange 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] to create [Full Name]. Next, we used that Alias name. It means following records will be sorted by the [Full Name] in the Descending.

SQL Ascending and Descending in Same Statement

It also allows us to combine the ASC and DESC keywords in a single statement. In this ASC DESC sorts example, we are sorting by [YearlyIncome] in the Ascending order, and then by [Occupation] in the Descending.

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

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

SQL Order By Case Statement

In this 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 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 Group By Clause Example

In this example, we used the Group By Clause. This combination of group by and this one can help you to arrange the Grouped or aggregated text.

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.

Order By Top Clause

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