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 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 ORDER BY Clause.
SQL ORDER BY ASC
To sort the data in ascending, we have to use this 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 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. Management can use this data to try different strategies to improve company sales.
Single Column in Asc
The following query will order customer’s data by 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 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.
String in Ascending
In this example, we used an Education string column to arrange the Data. It will sort them in Alphabetical order using Education.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Customer]
ORDER BY [Education] ASC
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 the Occupation.
Ascending using Alias Column
In this example, we will 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 Alias’s name. It means customers sorted by the [Full Name].
Order By Multiple Columns in Ascending
In this multiple columns example, we are sorting the customers by [YearlyIncome], and then by [Occupation]. Both the 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 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 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 and display records in descending.
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.
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.
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. 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
This 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.
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 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.
Order By Top Clause
In this example, we used the Top Clause. It selects the first 8 records organized by yearly income in descending.
Comments are closed.