Which SQL keyword is used to sort the result table by the values in one or more columns?
|
This article will cover the SQL ORDER BY clause including syntax, usage scenarios to sort out results in a Select statement. Show
Once we execute a Select statement in SQL Server, it returns unsorted results. We can define a sequence of a column in the select statement column list. We might need to sort out the result set based on a particular column value, condition etc. We can sort results in ascending or descending order with an ORDER BY clause in Select statement. We can see the syntax for SQL Order by clause as follows.
In SQL ORDER BY clause, we need to define ascending or descending order in which result needs to be sorted.
By default, SQL Server sorts out results using ORDER BY clause in ascending order. Specifying ASC in order by clause is optional. Let us explore the SQL ORDER BY clause using examples. In this article, I am using AdventureWorks2017 database for all examples. Execute the following query in SSMS. We are not using ORDER BY clause in this query.
Example 1: Sort results on a column defined in a Select statement using SQL Order By clauseSuppose our requirement is to sort the result by BirthDate column. This column is also specified in the column list of Select statement.
It uses a default sort method (ascending) because we have not specified any sort order in this query.
 If we want to sort out results in descending order on birthdate column, we can specify DESC in order by clause.
Example 2: Sort results on a column not defined in a Select statement using SQL Order By clauseIn the previous example, we sorted out results on the birthdate column. We have specified this column in select statement column list as well. Suppose we want to sort out results on birthday column, but this column is not specified in the select statement. We can still sort results on a column not defined in a Select statement.
Example 3: Sort results by column positions in a Select statement using SQL Order By clauseIn previous examples, we specified the column name in Order by clause to sort results in ascending or descending order. We can also specify column position in Order by clause.
In this query, column birthdate is at the 3rd position; therefore, we can use three in the Order by clause to sort results on this column data.
Note: I would not recommend using column position in Order By clause. You should always use a column name in Order by clause.
Example 4: Sort results on multiple columns in a Select statement using SQL Order By clauseWe can sort results based on multiple columns as well in a select statement. Suppose we want to get results in the following the order.
We can specify both ascending and descending order on both columns as shown in the following query.
In the following screenshot, you can see the result is sorted in ascending order for SickLeaveHours. If there are multiple rows with the same value for SickLeaveHours, it further sorts results on Vacationhours in descending order.
Example 5: Sort results on alias columns in a Select statement using SQL Order By clauseMany times, we define an alias on a column in a Select statement. Suppose you want to get maximum value in a column using the max function. We can specify a column name to appear in the output. If we do not specify any column name, we get the output without any column name.
In the following screenshot, you can see we get the output without any column name.
Let’s rerun query using an alias.
We can use the Order by clause for an alias column as well. Suppose we want to define an alias on SickLeaveHours as [SickHours]. Further, we want to use this alias in Order by clause to sort results in descending order.
In the following screenshot, We can see alias column SickHours sorted in descending order.
Example 6: Sort results with expression in a Select statement using SQL Order By clauseWe can use expressions as well in Order by clause. Suppose we want to sort for a year in a date column. Let us run the query without any Order by clause first.
Now, we want to sort on Birthdate column yearly in ascending order. I am using the TOP clause to limit result in the output. We are using DATEPART function we get a specified part of a date. In this query, we want to extract year to sort data using the DATEPART function.
Example 6: Sort results with Collation using SQL Order By clauseSuppose we have a table that contains column having case sensitive data. We might want to sort results specifying the collation in Order by clause. Let us create a table with Latin1_General_CI_AI collation and later we will sort it using another collation Latin1_General_CS_AS. You can find the definition of both collations in the following table.
Let us sort out results without specifying any sort condition. It uses the collation defined in the EmpName column (Latin1_General_CI_AI). Query 1:
Query 2:
In the following output, you can see a difference in the result of both queries. In the Query1, it uses default column collation (Latin1_General_CI_AI) for sorting results. In Query2, it uses collation (Latin1_General_CS_AS) specified in Order by clause to sort results.
Example 7: Sort results using a Rank function using SQL Order By clauseWe can use built-in Ranking functions in SQL Server with Order by clause as well. We can use Rank functions to provide a rank in a set of rows. We have following Rank functions in SQL Server.
Let us explore the use of Order By clause with each Ranking functions. ROW_NUMBER We can use ROW_NUMBER to provide row number in a specified column based on Order By clause. In the following query, we want to get row number for SickLeaveHours column values in ascending order.
RANK: In Rank function, we get a rank for each row based on column value specified. If there are multiple rows with a similar value, it gives the same rank but skips the next number in the sequence. In the following query, we specified Row_Number() and RANK() function along with Order by clause for SickLeaveHours column.
In the following screenshot, we can see for result sorted out using SickLeaveHours. In the Rank function, it skips value 2-3 because we have 3 rows for SickLeaveHours 20.
Similarly, we can use DENSE_RANK() and NTILE(4) with Order by clause in a select statement.
In the output, we can see DENSE_RANK() gives a rank for each row based on the conditions specified in Order by clause. It does not skip the next value in rank if we have multiple rows with similar values. The NTILE function divides the complete result sets into the number of groups specified.
Example 8: Limit number of rows using SQL Order By clauseWe can skip some rows with OFFSET and FETCH in an Order by clause. First, let us run the following query and view the output in SSMS.
In the following screenshot, we can see this query returns 238 rows.
Suppose we want to Skip the first 100 rows from the sorted result set and return all remaining rows. We can use OFFSET with Order by clause.
In the output, we get 138 rows because it skips first 100 rows from the sorted result.
Suppose we want to skip 100 rows from the sorted result. We further need only first 20 rows from the result set. We can specify the following values along with Order by clause.
Execute the following query and view the output.
In the output, we can see that only the first 20 rows after skipping 100 records in sorted result.
Note: We can use OFFSET and FETCH in SQL Order By clause starting from SQL Server 2012.
Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience. I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines. I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020. Personal Blog:
https://www.dbblogger.com View all posts by Rajendra Gupta Which SQL keyword starts an SQL statement to SELECT fields and records from one or more related tables?SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT… INTO statements. You can use an asterisk (*) to select all fields in a table.
Which SQL keyword is used to add one or more rows of data to a table?The INSERT INTO statement is used to insert new records in a table.
Which SQL keyword is used to determine if a column value is equal to any one of a set of values?In SQL, you can use the = operator to test for equality in a query. In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to Microsoft.
How do I sort a column in a table in SQL?You can sort your table data in ascending order using the ORDER BY clause in SQL. SELECT columns FROM table ORDER BY column; If you want to sort by descending order then you also have to use the DESC keyword.
|
