SELECT - Club BY Clause (Transact-SQL)

  • Article
  • 17 minutes to read

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesAnalytics Platform System (PDW)

Sorts data returned by a query in SQL Server. Use this clause to:

  • Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are non guaranteed unless an Society By clause is specified.

  • Determine the order in which ranking function values are applied to the effect set.

Topic link icon Transact-SQL Syntax Conventions

Note

ORDER Past is not supported in SELECT/INTO or CREATE Table Equally SELECT (CTAS) statements in Azure Synapse Analytics or Analytics Platform Organisation (PDW).

Syntax

              -- Syntax for SQL Server and Azure SQL Database      Guild BY order_by_expression       [ COLLATE collation_name ]        [ ASC | DESC ]        [ ,...n ]    [ <offset_fetch> ]      <offset_fetch> ::=   {        Starting time { integer_constant | offset_row_count_expression } { ROW | ROWS }       [         FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY       ]   }                          
              -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse      [ Order BY        {       order_by_expression        [ ASC | DESC ]        } [ ,...n ]    ]

Arguments

order_by_expression
Specifies a cavalcade or expression on which to sort the query outcome set. A sort column can be specified as a proper name or cavalcade allonym, or a nonnegative integer representing the position of the column in the select list.

Multiple sort columns can exist specified. Column names must be unique. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted outcome set up. That is, the result set is sorted by the first column and so that ordered list is sorted by the second column, and so on.

The column names referenced in the ORDER Past clause must correspond to either a column or cavalcade alias in the select list or to a cavalcade defined in a table specified in the FROM clause without any ambiguities. If the Social club By clause references a cavalcade alias from the select list, the cavalcade alias must exist used standalone, and not as a office of some expression in ORDER BY clause, for example:

              SELECT SCHEMA_NAME(schema_id) AS SchemaName FROM sys.objects  ORDER BY SchemaName; -- correct  SELECT SCHEMA_NAME(schema_id) Equally SchemaName FROM sys.objects  Guild BY SchemaName + ''; -- wrong

COLLATE collation_name
Specifies that the Guild BY performance should be performed according to the collation specified in collation_name, and not according to the collation of the cavalcade as divers in the tabular array or view. collation_name can exist either a Windows collation name or a SQL collation name. For more than information, see Collation and Unicode Back up. COLLATE is applicable only for columns of type char, varchar, nchar, and nvarchar.

ASC | DESC
Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort order. Nada values are treated every bit the lowest possible values.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of rows to skip before it starts to return rows from the query expression. The value tin can be an integer abiding or expression that is greater than or equal to zero.

Applies to: SQL Server 2012 (xi.x) and later and Azure SQL Database.south

offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns divers in the outer query telescopic. That is, information technology cannot be correlated with the outer query.

ROW and ROWS are synonyms and are provided for ANSI compatibility.

In query execution plans, the offset row count value is displayed in the Kickoff aspect of the Height query operator.

FETCH { FIRST | Adjacent } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
Specifies the number of rows to return afterwards the Commencement clause has been processed. The value can be an integer abiding or expression that is greater than or equal to ane.

Applies to: SQL Server 2012 (11.ten) and later and Azure SQL Database.

fetch_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, information technology cannot reference whatsoever columns divers in the outer query telescopic. That is, it cannot be correlated with the outer query.

FIRST and Side by side are synonyms and are provided for ANSI compatibility.

ROW and ROWS are synonyms and are provided for ANSI compatibility.

In query execution plans, the beginning row count value is displayed in the Rows or Summit attribute of the TOP query operator.

Best Practices

Avert specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For instance, although a argument such equally SELECT ProductID, Proper name FROM Product.Production Guild Past ii is valid, the statement is not every bit easily understood by others compared with specifying the actual column name. In addition, changes to the select list, such equally irresolute the column guild or adding new columns, requires modifying the ORDER BY clause in order to avoid unexpected results.

In a SELECT TOP (N) statement, ever use an ORDER By clause. This is the only way to predictably bespeak which rows are affected past Tiptop. For more information, see TOP (Transact-SQL).

Interoperability

When used with a SELECT...INTO or INSERT...SELECT statement to insert rows from another source, the Club Past clause does not guarantee the rows are inserted in the specified social club.

Using OFFSET and FETCH in a view does not change the updateability holding of the view.

Limitations and Restrictions

There is no limit to the number of columns in the Society BY clause; however, the full size of the columns specified in an ORDER BY clause cannot exceed 8,060 bytes.

Columns of type ntext, text, epitome, geography, geometry, and xml cannot be used in an ORDER Past clause.

An integer or constant cannot be specified when order_by_expression appears in a ranking function. For more data, come across OVER Clause (Transact-SQL).

If a table name is aliased in the FROM clause, simply the allonym name can be used to qualify its columns in the Guild Past clause.

Cavalcade names and aliases specified in the Guild BY clause must be defined in the select list if the SELECT statement contains one of the post-obit clauses or operators:

  • Spousal relationship operator

  • EXCEPT operator

  • INTERSECT operator

  • SELECT DISTINCT

Additionally, when the statement includes a UNION, EXCEPT, or INTERSECT operator, the column names, or cavalcade aliases must exist specified in the select list of the first (left-side) query.

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY is allowed just at the end of the statement. This brake applies simply to when you lot specify Spousal relationship, EXCEPT, and INTERSECT in a top-level query and non in a subquery. Run into the Examples section that follows.

The Lodge BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When Lodge By is used in these objects, the clause is used just to determine the rows returned by the Elevation clause or First and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER Past is likewise specified in the query itself.

Offset and FETCH are not supported in indexed views or in a view that is divers past using the CHECK Option clause.

Outset and FETCH can be used in any query that allows TOP and Gild BY with the following limitations:

  • The OVER clause does not back up OFFSET and FETCH.

  • Kickoff and FETCH cannot be specified straight in INSERT, UPDATE, MERGE, and DELETE statements, but can be specified in a subquery defined in these statements. For case, in the INSERT INTO SELECT argument, Showtime and FETCH can be specified in the SELECT statement.

  • In a query that uses Marriage, EXCEPT or INTERSECT operators, OFFSET and FETCH can only be specified in the final query that specifies the order of the query results.

  • Superlative cannot be combined with OFFSET and FETCH in the same query expression (in the same query scope).

Using OFFSET and FETCH to limit the rows returned

We recommend that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.

Using Get-go and FETCH equally a paging solution requires running the query one time for each "page" of data returned to the customer application. For example, to return the results of a query in 10-row increments, yous must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to xx and then on. Each query is independent and non related to each other in any fashion. This ways that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking country. To achieve stable results between query requests using Showtime and FETCH, the following weather condition must be met:

  1. The underlying information that is used by the query must not modify. That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a unmarried transaction using either snapshot or serializable transaction isolation. For more data nigh these transaction isolation levels, see Fix TRANSACTION ISOLATION LEVEL (Transact-SQL).

  2. The ORDER BY clause contains a cavalcade or combination of columns that are guaranteed to be unique.

Run across the case "Running multiple queries in a single transaction" in the Examples section after in this topic.

If consequent execution plans are important in your paging solution, consider using the OPTIMIZE FOR query hint for the Kickoff and FETCH parameters. See "Specifying expressions for Start and FETCH values" in the Examples department later in this topic. For more information about OPTIMIZE FOR, see Query Hints (Transact-SQL).

Examples

Category Featured syntax elements
Basic syntax Lodge Past
Specifying ascending and descending order DESC • ASC
Specifying a collation COLLATE
Specifying a conditional order Instance expression
Using Guild BY in a ranking role Ranking functions
Limiting the number of rows returned Starting time • FETCH
Using ORDER Past with UNION, EXCEPT, and INTERSECT Matrimony

Basic syntax

Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.

A. Specifying a single column defined in the select listing

The post-obit example orders the result set by the numeric ProductID column. Because a specific sort society is non specified, the default (ascending order) is used.

              Apply AdventureWorks2012;   GO   SELECT ProductID, Proper name FROM Production.Product   WHERE Proper noun Similar 'Lock Washer%'   Guild Past ProductID;

B. Specifying a column that is not defined in the select list

The post-obit example orders the result set up by a column that is non included in the select list, just is defined in the table specified in the FROM clause.

              Use AdventureWorks2012;   Go   SELECT ProductID, Proper name, Colour   FROM Product.Production   Guild By ListPrice;

C. Specifying an alias equally the sort column

The post-obit example specifies the cavalcade alias SchemaName every bit the sort order column.

              Utilise AdventureWorks2012;   Get   SELECT proper noun, SCHEMA_NAME(schema_id) Every bit SchemaName   FROM sys.objects   WHERE type = 'U'   Lodge By SchemaName;

D. Specifying an expression equally the sort column

The following example uses an expression equally the sort column. The expression is divers by using the DATEPART role to sort the consequence prepare by the year in which employees were hired.

              USE AdventureWorks2012;   GO   SELECT BusinessEntityID, JobTitle, HireDate   FROM HumanResources.Employee   Gild BY DATEPART(year, HireDate);

Specifying ascending and descending sort order

A. Specifying a descending guild

The post-obit example orders the result set by the numeric column ProductID in descending gild.

              USE AdventureWorks2012;   Get   SELECT ProductID, Name FROM Production.Product   WHERE Name Like 'Lock Washer%'   Gild By ProductID DESC;

B. Specifying an ascending order

The post-obit example orders the result gear up by the Name column in ascending lodge. The characters are sorted alphabetically, non numerically. That is, 10 sorts before two.

              USE AdventureWorks2012;   GO   SELECT ProductID, Proper name FROM Product.Product   WHERE Proper name LIKE 'Lock Washer%'   Guild BY Name ASC ;

C. Specifying both ascending and descending order

The following example orders the issue set past two columns. The query result set is first sorted in ascending social club by the FirstName column and then sorted in descending order by the LastName column.

              Apply AdventureWorks2012;   GO   SELECT LastName, FirstName FROM Person.Person   WHERE LastName LIKE 'R%'   ORDER Past FirstName ASC, LastName DESC ;

Specifying a collation

The following instance shows how specifying a collation in the ORDER By clause can change the society in which the query results are returned. A table is created that contains a cavalcade divers by using a case-insensitive, accent-insensitive collation. Values are inserted with a diversity of case and accent differences. Because a collation is not specified in the Gild By clause, the showtime query uses the collation of the column when sorting the values. In the second query, a example-sensitive, emphasis-sensitive collation is specified in the Social club Past clause, which changes the order in which the rows are returned.

              Employ tempdb;   Go   CREATE TABLE #t1 (proper name NVARCHAR(xv) COLLATE Latin1_General_CI_AI)   GO   INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(North'sanchez');      -- This query uses the collation specified for the cavalcade 'name' for sorting.   SELECT name   FROM #t1   Guild By proper noun;   -- This query uses the collation specified in the Guild By clause for sorting.   SELECT name   FROM #t1   ORDER Past proper noun COLLATE Latin1_General_CS_AS;

Specifying a conditional guild

The following examples use the CASE expression in an Guild BY clause to conditionally decide the sort social club of the rows based on a given column value. In the first instance, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that have the SalariedFlag set to 1 are returned in society by the BusinessEntityID in descending order. Employees that have the SalariedFlag set up to 0 are returned in order by the BusinessEntityID in ascending order. In the second case, the issue gear up is ordered by the column TerritoryName when the column CountryRegionName is equal to 'U.s.a.' and by CountryRegionName for all other rows.

              SELECT BusinessEntityID, SalariedFlag   FROM HumanResources.Employee   ORDER BY Example SalariedFlag WHEN one THEN BusinessEntityID END DESC           ,Case WHEN SalariedFlag = 0 So BusinessEntityID Cease;   GO                          
              SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName   FROM Sales.vSalesPerson   WHERE TerritoryName IS NOT Nothing   Lodge By CASE CountryRegionName WHEN 'United States' THEN TerritoryName            ELSE CountryRegionName Cease;

Using Lodge Past in a ranking office

The following example uses the Order Past clause in the ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

              Employ AdventureWorks2012;   GO   SELECT p.FirstName, p.LastName       ,ROW_NUMBER() OVER (Club BY a.PostalCode) AS "Row Number"       ,RANK() OVER (ORDER BY a.PostalCode) Equally "Rank"       ,DENSE_RANK() OVER (Society BY a.PostalCode) Equally "Dense Rank"       ,NTILE(4) OVER (Club Past a.PostalCode) AS "Quartile"       ,s.SalesYTD, a.PostalCode   FROM Sales.SalesPerson Every bit s        INNER Join Person.Person As p            ON s.BusinessEntityID = p.BusinessEntityID       INNER Join Person.Accost As a            ON a.AddressID = p.BusinessEntityID   WHERE TerritoryID IS Not NULL AND SalesYTD <> 0;

Limiting the number of rows returned

The post-obit examples use OFFSET and FETCH to limit the number of rows returned by a query.

Applies to: SQL Server 2012 (11.x) and later on and Azure SQL Database.

A. Specifying integer constants for OFFSET and FETCH values

The following example specifies an integer constant every bit the value for the Starting time and FETCH clauses. The get-go query returns all rows sorted by the column DepartmentID. Compare the results returned by this query with the results of the two queries that follow it. The side by side query uses the clause OFFSET v ROWS to skip the first 5 rows and render all remaining rows. The final query uses the clause Get-go 0 ROWS to showtime with the beginning row and so uses FETCH Next 10 ROWS Merely to limit the rows returned to 10 rows from the sorted result fix.

              USE AdventureWorks2012;   Go   -- Return all rows sorted by the column DepartmentID.   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   Lodge By DepartmentID;      -- Skip the starting time v rows from the sorted issue gear up and return all remaining rows.   SELECT DepartmentID, Proper name, GroupName   FROM HumanResources.Department   ORDER BY DepartmentID Outset 5 ROWS;      -- Skip 0 rows and return only the starting time ten rows from the sorted consequence set.   SELECT DepartmentID, Proper name, GroupName   FROM HumanResources.Department   Society BY DepartmentID        OFFSET 0 ROWS       FETCH NEXT 10 ROWS Just;

B. Specifying variables for OFFSET and FETCH values

The post-obit example declares the variables @RowsToSkip and @FetchRows and specifies these variables in the OFFSET and FETCH clauses.

              USE AdventureWorks2012;   GO   -- Specifying variables for Kickoff and FETCH values     DECLARE @RowsToSkip TINYINT = 2       , @FetchRows TINYINT = 8;   SELECT DepartmentID, Proper noun, GroupName   FROM HumanResources.Section   Society BY DepartmentID ASC        OFFSET @RowsToSkip ROWS        FETCH NEXT @FetchRows ROWS But;

C. Specifying expressions for OFFSET and FETCH values

The following example uses the expression @StartingRowNumber - one to specify the Showtime value and the expression @EndingRowNumber - @StartingRowNumber + 1 to specify the FETCH value. In add-on, the query hint, OPTIMIZE FOR, is specified. This hint tin be used to provide a particular value for a local variable when the query is compiled and optimized. The value is used just during query optimization, and not during query execution. For more information, come across Query Hints (Transact-SQL).

              Utilize AdventureWorks2012;   GO      -- Specifying expressions for OFFSET and FETCH values       DECLARE @StartingRowNumber TINYINT = 1         , @EndingRowNumber TINYINT = 8;   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   Gild By DepartmentID ASC        OFFSET @StartingRowNumber - 1 ROWS        FETCH Side by side @EndingRowNumber - @StartingRowNumber + i ROWS Merely   Choice ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

D. Specifying a constant scalar subquery for Commencement and FETCH values

The post-obit instance uses a abiding scalar subquery to define the value for the FETCH clause. The subquery returns a unmarried value from the column PageSize in the table dbo.AppSettings.

              -- Specifying a constant scalar subquery   USE AdventureWorks2012;   Get   CREATE Tabular array dbo.AppSettings (AppSettingID INT Non NULL, PageSize INT Non NULL);   GO   INSERT INTO dbo.AppSettings VALUES(ane, ten);   Go   DECLARE @StartingRowNumber TINYINT = ane;   SELECT DepartmentID, Proper noun, GroupName   FROM HumanResources.Department   Social club BY DepartmentID ASC        OFFSET @StartingRowNumber ROWS        FETCH Side by side (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = i) ROWS ONLY;

E. Running multiple queries in a single transaction

The following example shows one method of implementing a paging solution that ensures stable results are returned in all requests from the query. The query is executed in a single transaction using the snapshot isolation level, and the column specified in the ORDER Past clause ensures cavalcade uniqueness.

              Utilise AdventureWorks2012;   Go      -- Ensure the database tin support the snapshot isolation level gear up for the query.   IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0       ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;   GO      -- Set the transaction isolation level  to SNAPSHOT for this query.   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;   Become      -- Showtime the transaction. Brainstorm TRANSACTION;   Get   -- Declare and set the variables for the Start and FETCH values.   DECLARE @StartingRowNumber INT = 1         , @RowCountPerPage INT = 3;      -- Create the condition to terminate the transaction subsequently all rows have been returned.   WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber   BEGIN      -- Run the query until the cease condition is met.   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   ORDER BY DepartmentID ASC        OFFSET @StartingRowNumber - 1 ROWS        FETCH Next @RowCountPerPage ROWS ONLY;      -- Increment @StartingRowNumber value.   SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;   Go along   END;   Go   COMMIT TRANSACTION;   Become

Using Society Past with UNION, EXCEPT, and INTERSECT

When a query uses the Matrimony, EXCEPT, or INTERSECT operators, the ORDER BY clause must be specified at the cease of the argument and the results of the combined queries are sorted. The following example returns all products that are red or yellow and sorts this combined list past the column ListPrice.

              Use AdventureWorks2012;   Become   SELECT Proper name, Color, ListPrice   FROM Product.Product   WHERE Color = 'Red'   -- Social club BY cannot be specified hither.   UNION ALL   SELECT Name, Colour, ListPrice   FROM Production.Production   WHERE Color = 'Yellow'   Society BY ListPrice ASC;

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

The following example demonstrates ordering of a result set past the numerical EmployeeKey column in ascending order.

              -- Uses AdventureWorks      SELECT EmployeeKey, FirstName, LastName FROM DimEmployee   WHERE LastName LIKE 'A%'   ORDER Past EmployeeKey;

The following example orders a result set by the numerical EmployeeKey cavalcade in descending order.

              -- Uses AdventureWorks      SELECT EmployeeKey, FirstName, LastName FROM DimEmployee   WHERE LastName LIKE 'A%'   ORDER Past EmployeeKey DESC;

The following example orders a result ready by the LastName column.

              -- Uses AdventureWorks      SELECT EmployeeKey, FirstName, LastName FROM DimEmployee   WHERE LastName LIKE 'A%'   ORDER Past LastName;

The following example orders past 2 columns. This query first sorts in ascending order by the FirstName column, so sorts common FirstName values in descending order past the LastName column.

              -- Uses AdventureWorks      SELECT EmployeeKey, FirstName, LastName FROM DimEmployee   WHERE LastName LIKE 'A%'   Club Past LastName, FirstName;

Run into Also

Expressions (Transact-SQL)
SELECT (Transact-SQL)
FROM (Transact-SQL)
Ranking Functions (Transact-SQL)
Peak (Transact-SQL)
Query Hints (Transact-SQL)
EXCEPT and INTERSECT (Transact-SQL)
UNION (Transact-SQL)
CASE (Transact-SQL)