SQL ORDER BY Clause

The SQL ORDER BY Clause is helpful for sorting the data in either Ascending or Descending order. In Tables, By default, data will not insert in any order unless you have any indexes. So, If you want to retrieve the data in any particular way, you have to sort the data using this SQL ORDER BY Clause along with the Select statement and ASC or DESC keywords.

SQL ORDER BY Clause Syntax

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 Positions of the Columns or Alias names in the statement. And also, we can use one or more columns. We use the below data to explain the SQL Server ORDER BY Clause.

SQL ORDER BY ASC

To sort the data in ascending, we have to use this SQL Server order by clause statement, followed by the ASC keyword. For example, 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, your job is to identify low-performing products (Products with No Sales or fewer sales). You can write the query as Select Product Name from Products Table categorizes the Sales in Asc.

By seeing the result, you can understand that x is not performing well, and y has no sales at all. Management can use this data to try different strategies to improve company sales.

SQL ORDER BY Clause Single Column in Asc

The following query will order customer’s data by 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 arranges records alphabetically because it is the default sort order. That’s why it is optional to use the ASC keyword. For example, we used the previous query without ASC keywords in the following statement.

Order By String or Varchar Columns in Ascending

In this example, we used an Education string column to arrange the Data. This SQL query will sort them in Alphabetical order using Education.

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

SQL Order by Number in Asc (Numeric Position)

The following example sorts 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 the fourth column, so the SQL will arrange the records by Occupation.

SQL ORDER BY Ascending using Alias Column

In this example, we will arrange the result in Ascending using the Alias Name.

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

SQL Order By Multiple Columns in Ascending

In this multiple columns example, we are sorting the customers by [YearlyIncome], and then by [Occupation]. Both columns will be in the ASC. It means we used more than one column to sort in a particular format.

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 keyword SQL ORDER BY Clause DESC. For example, If you are searching for a shoe on Amazon when you type shoe in the search bar, it will display the shoes by Rating. It means Shoes are sorted as per the Rating (Technically, Selecting Shoes from the Product table organized by Product Rating in descending).

The following are some of the total number of ways we can use and display records in descending.

SQL ORDER BY Single Column in Descending

In this example query, we used the Yearly Income. It means orders 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 the Customer table sorts by the [YearlyIncome] in the Descending.

Order By String Column 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.

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

Order By 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. For example, the Numerical position of a Sales is 7. So the customers will be arranged by this Sales in the Descending.

Using Alias Column as Order By Desc

This SQL ORDER BY Clause example uses 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 Alias’s name. The following records will be sorted by the [Full Name] in the Descending.

SQL ORDER BY 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 by [YearlyIncome] in Ascending order, and then by [Occupation] in Descending.

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

Let us use the SQL ORDER BY Clause DESC keyword first and ASC keyword second to arrange in descending and ascending. It sorts the Customers by [YearlyIncome] in the Descending, and then by [Education] in the Asc.

SQL Order By Case Statement

In this example, we used the Case Statement. It will help you to create your own (Custom one).

The below code will arrange the customer by Occupation column. For example, 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 the group by and this can help you arrange the Grouped or aggregated text.

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

SQL Order By Top Clause

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