SELECT - ORDER Past 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 Organisation (PDW)

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

  • Society the consequence set of a query by the specified column listing and, optionally, limit the rows returned to a specified range. The club in which rows are returned in a result ready are non guaranteed unless an Social club By clause is specified.

  • Determine the club in which ranking role values are applied to the outcome set.

Topic link icon Transact-SQL Syntax Conventions

Note

ORDER Past is non supported in SELECT/INTO or CREATE TABLE AS SELECT (CTAS) statements in Azure Synapse Analytics or Analytics Platform Arrangement (PDW).

Syntax

              -- Syntax for SQL Server and Azure SQL Database      ORDER 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 { Outset | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } Simply       ]   }                          
              -- Syntax for Azure Synapse Analytics and Parallel Data Warehouse      [ ORDER By        {       order_by_expression        [ ASC | DESC ]        } [ ,...n ]    ]

Arguments

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

Multiple sort columns tin be specified. Column names must exist unique. The sequence of the sort columns in the ORDER BY clause defines the arrangement of the sorted result set. That is, the result fix is sorted by the first column and then that ordered list is sorted past the second column, and and then on.

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

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

COLLATE collation_name
Specifies that the ORDER By performance should exist performed according to the collation specified in collation_name, and not according to the collation of the cavalcade equally defined in the tabular array or view. collation_name tin be either a Windows collation name or a SQL collation proper noun. For more information, see Collation and Unicode Support. COLLATE is applicative but 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 club. ASC sorts from the lowest value to highest value. DESC sorts from highest value to lowest value. ASC is the default sort gild. Null values are treated as the lowest possible values.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of rows to skip earlier information technology starts to render rows from the query expression. The value tin can exist an integer abiding or expression that is greater than or equal to zero.

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

offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference whatsoever columns defined in the outer query telescopic. That is, it cannot exist 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 Offset attribute of the Summit query operator.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } Simply
Specifies the number of rows to return subsequently the OFFSET clause has been processed. The value tin be an integer abiding or expression that is greater than or equal to ane.

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

fetch_row_count_expression tin be a variable, parameter, or constant scalar subquery. When a subquery is used, information technology cannot reference whatever columns defined in the outer query scope. That is, it cannot exist correlated with the outer query.

FIRST and Next are synonyms and are provided for ANSI compatibility.

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

In query execution plans, the offset row count value is displayed in the Rows or Top aspect of the TOP query operator.

All-time Practices

Avoid specifying integers in the Gild Past clause equally positional representations of the columns in the select listing. For case, although a statement such equally SELECT ProductID, Name FROM Product.Production ORDER Past 2 is valid, the statement is not as easily understood past others compared with specifying the actual column proper noun. In addition, changes to the select list, such as changing the column lodge or adding new columns, requires modifying the ORDER By clause in social club to avert unexpected results.

In a SELECT Acme (N) statement, always use an ORDER BY clause. This is the only way to predictably indicate which rows are affected by Peak. For more information, run into Pinnacle (Transact-SQL).

Interoperability

When used with a SELECT...INTO or INSERT...SELECT statement to insert rows from another source, the Gild BY clause does non guarantee the rows are inserted in the specified order.

Using OFFSET and FETCH in a view does not modify the updateability property of the view.

Limitations and Restrictions

In that location is no limit to the number of columns in the Gild BY clause; however, the total size of the columns specified in an ORDER BY clause cannot exceed 8,060 bytes.

Columns of blazon ntext, text, epitome, geography, geometry, and xml cannot exist used in an Club By clause.

An integer or constant cannot be specified when order_by_expression appears in a ranking part. For more information, see OVER Clause (Transact-SQL).

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

Column names and aliases specified in the Guild Past clause must exist defined in the select list if the SELECT statement contains one of the following clauses or operators:

  • Union operator

  • EXCEPT operator

  • INTERSECT operator

  • SELECT Distinct

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

In a query that uses UNION, EXCEPT, or INTERSECT operators, Guild BY is allowed only at the stop of the statement. This restriction applies only to when yous specify UNION, EXCEPT, and INTERSECT in a top-level query and not in a subquery. See the Examples section that follows.

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

Beginning and FETCH are not supported in indexed views or in a view that is defined by using the Check Pick clause.

Outset and FETCH tin can be used in whatsoever query that allows Top and ORDER BY with the following limitations:

  • The OVER clause does not back up Get-go and FETCH.

  • Beginning and FETCH cannot be specified directly in INSERT, UPDATE, MERGE, and DELETE statements, but can exist specified in a subquery defined in these statements. For example, in the INSERT INTO SELECT statement, OFFSET and FETCH can exist specified in the SELECT statement.

  • In a query that uses UNION, EXCEPT or INTERSECT operators, First and FETCH tin can only exist specified in the final query that specifies the lodge of the query results.

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

Using Offset and FETCH to limit the rows returned

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

Using OFFSET and FETCH equally a paging solution requires running the query in one case for each "page" of data returned to the customer application. For case, to render the results of a query in 10-row increments, you must execute the query once to render rows 1 to ten and then run the query again to return rows 11 to twenty and and then on. Each query is independent and non related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client awarding is responsible for tracking country. To reach stable results between query requests using OFFSET and FETCH, the following conditions must be met:

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

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

See the example "Running multiple queries in a single transaction" in the Examples section later in this topic.

If consistent execution plans are important in your paging solution, consider using the OPTIMIZE FOR query hint for the Offset and FETCH parameters. Come across "Specifying expressions for OFFSET and FETCH values" in the Examples section later in this topic. For more than information most OPTIMIZE FOR, see Query Hints (Transact-SQL).

Examples

Category Featured syntax elements
Basic syntax Gild By
Specifying ascending and descending lodge DESC • ASC
Specifying a collation COLLATE
Specifying a conditional order Example expression
Using ORDER BY in a ranking role Ranking functions
Limiting the number of rows returned Commencement • FETCH
Using Club BY with Wedlock, EXCEPT, and INTERSECT Spousal relationship

Bones syntax

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

A. Specifying a single column defined in the select listing

The following example orders the effect set past the numeric ProductID column. Because a specific sort order is not specified, the default (ascending order) is used.

              Utilise AdventureWorks2012;   Become   SELECT ProductID, Name FROM Production.Production   WHERE Name LIKE 'Lock Washer%'   ORDER BY ProductID;

B. Specifying a cavalcade that is non defined in the select list

The post-obit example orders the outcome ready by a column that is non included in the select list, but is defined in the table specified in the FROM clause.

              USE AdventureWorks2012;   Become   SELECT ProductID, Name, Colour   FROM Production.Product   ORDER By ListPrice;

C. Specifying an alias equally the sort column

The post-obit example specifies the column alias SchemaName as the sort society cavalcade.

              Utilise AdventureWorks2012;   GO   SELECT name, SCHEMA_NAME(schema_id) As SchemaName   FROM sys.objects   WHERE type = 'U'   Order By SchemaName;

D. Specifying an expression as the sort column

The following example uses an expression as the sort column. The expression is defined by using the DATEPART office to sort the result fix by the year in which employees were hired.

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

Specifying ascending and descending sort guild

A. Specifying a descending order

The following example orders the upshot set up by the numeric column ProductID in descending social club.

              USE AdventureWorks2012;   Go   SELECT ProductID, Name FROM Product.Product   WHERE Name Similar 'Lock Washer%'   ORDER BY ProductID DESC;

B. Specifying an ascending society

The following case orders the consequence ready by the Name cavalcade in ascending club. The characters are sorted alphabetically, not numerically. That is, ten sorts before 2.

              USE AdventureWorks2012;   Become   SELECT ProductID, Name FROM Production.Production   WHERE Name LIKE 'Lock Washer%'   Guild By Name ASC ;

C. Specifying both ascending and descending club

The following example orders the result set by two columns. The query consequence gear up is offset sorted in ascending club by the FirstName column and and so sorted in descending order by the LastName column.

              USE AdventureWorks2012;   Get   SELECT LastName, FirstName FROM Person.Person   WHERE LastName LIKE 'R%'   ORDER BY FirstName ASC, LastName DESC ;

Specifying a collation

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

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

Specifying a provisional guild

The following examples use the CASE expression in an Lodge Past clause to conditionally determine the sort order of the rows based on a given cavalcade value. In the first example, the value in the SalariedFlag cavalcade of the HumanResources.Employee tabular array is evaluated. Employees that have the SalariedFlag set to 1 are returned in gild past the BusinessEntityID in descending gild. Employees that take the SalariedFlag ready to 0 are returned in order by the BusinessEntityID in ascending gild. In the 2d example, the result ready is ordered past the column TerritoryName when the column CountryRegionName is equal to 'Us' and by CountryRegionName for all other rows.

              SELECT BusinessEntityID, SalariedFlag   FROM HumanResources.Employee   ORDER BY CASE SalariedFlag WHEN ane THEN BusinessEntityID Finish DESC           ,Instance WHEN SalariedFlag = 0 And then BusinessEntityID Terminate;   GO                          
              SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName   FROM Sales.vSalesPerson   WHERE TerritoryName IS Non Aught   ORDER BY Example CountryRegionName WHEN 'United States' THEN TerritoryName            ELSE CountryRegionName END;

Using Gild By in a ranking function

The post-obit example uses the ORDER Past clause in the ranking functions ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

              Utilize AdventureWorks2012;   Become   SELECT p.FirstName, p.LastName       ,ROW_NUMBER() OVER (ORDER Past a.PostalCode) Every bit "Row Number"       ,RANK() OVER (Social club BY a.PostalCode) AS "Rank"       ,DENSE_RANK() OVER (Guild BY a.PostalCode) Equally "Dense Rank"       ,NTILE(iv) OVER (ORDER By a.PostalCode) Every bit "Quartile"       ,s.SalesYTD, a.PostalCode   FROM Sales.SalesPerson As s        INNER JOIN Person.Person As p            ON s.BusinessEntityID = p.BusinessEntityID       INNER Bring together Person.Address As a            ON a.AddressID = p.BusinessEntityID   WHERE TerritoryID IS Non Nothing AND SalesYTD <> 0;

Limiting the number of rows returned

The following 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 as the value for the Starting time and FETCH clauses. The first query returns all rows sorted past the column DepartmentID. Compare the results returned past this query with the results of the two queries that follow it. The next query uses the clause OFFSET 5 ROWS to skip the first 5 rows and return all remaining rows. The final query uses the clause OFFSET 0 ROWS to start with the kickoff row so uses FETCH Side by side ten ROWS ONLY to limit the rows returned to 10 rows from the sorted consequence prepare.

              Use AdventureWorks2012;   GO   -- Return all rows sorted by the column DepartmentID.   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Section   Social club Past DepartmentID;      -- Skip the first 5 rows from the sorted result set and return all remaining rows.   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   ORDER BY DepartmentID Start v ROWS;      -- Skip 0 rows and render only the showtime 10 rows from the sorted effect fix.   SELECT DepartmentID, Proper name, GroupName   FROM HumanResources.Department   Guild BY DepartmentID        OFFSET 0 ROWS       FETCH Next 10 ROWS ONLY;

B. Specifying variables for Beginning and FETCH values

The following example declares the variables @RowsToSkip and @FetchRows and specifies these variables in the Starting time and FETCH clauses.

              Employ AdventureWorks2012;   GO   -- Specifying variables for Outset and FETCH values     DECLARE @RowsToSkip TINYINT = 2       , @FetchRows TINYINT = viii;   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   ORDER BY DepartmentID ASC        Commencement @RowsToSkip ROWS        FETCH Next @FetchRows ROWS Simply;

C. Specifying expressions for OFFSET and FETCH values

The following example uses the expression @StartingRowNumber - one to specify the OFFSET value and the expression @EndingRowNumber - @StartingRowNumber + 1 to specify the FETCH value. In addition, the query hint, OPTIMIZE FOR, is specified. This hint can be used to provide a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and non during query execution. For more information, see Query Hints (Transact-SQL).

              USE AdventureWorks2012;   GO      -- Specifying expressions for OFFSET and FETCH values       DECLARE @StartingRowNumber TINYINT = 1         , @EndingRowNumber TINYINT = 8;   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   Order By DepartmentID ASC        OFFSET @StartingRowNumber - 1 ROWS        FETCH Next @EndingRowNumber - @StartingRowNumber + one ROWS Only   OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = twenty) );

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

The post-obit example uses a constant scalar subquery to define the value for the FETCH clause. The subquery returns a single value from the column PageSize in the tabular array dbo.AppSettings.

              -- Specifying a constant scalar subquery   USE AdventureWorks2012;   Become   CREATE TABLE dbo.AppSettings (AppSettingID INT NOT Zip, PageSize INT NOT NULL);   Go   INSERT INTO dbo.AppSettings VALUES(1, 10);   GO   DECLARE @StartingRowNumber TINYINT = 1;   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   ORDER By DepartmentID ASC        Commencement @StartingRowNumber ROWS        FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

Due east. 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 Club BY clause ensures column uniqueness.

              USE AdventureWorks2012;   Become      -- Ensure the database can back up the snapshot isolation level set up for the query.   IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = North'AdventureWorks2012') = 0       Modify DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;   Go      -- Gear up the transaction isolation level  to SNAPSHOT for this query.   SET TRANSACTION ISOLATION LEVEL SNAPSHOT;   Get      -- Beginning the transaction. Begin TRANSACTION;   Get   -- Declare and ready the variables for the Get-go and FETCH values.   DECLARE @StartingRowNumber INT = 1         , @RowCountPerPage INT = three;      -- Create the condition to stop the transaction after all rows accept been returned.   WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber   Brainstorm      -- Run the query until the stop condition is met.   SELECT DepartmentID, Name, GroupName   FROM HumanResources.Department   ORDER By DepartmentID ASC        OFFSET @StartingRowNumber - 1 ROWS        FETCH Adjacent @RowCountPerPage ROWS ONLY;      -- Increment @StartingRowNumber value.   Set @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;   CONTINUE   END;   Become   COMMIT TRANSACTION;   GO

Using Social club BY with Matrimony, EXCEPT, and INTERSECT

When a query uses the UNION, EXCEPT, or INTERSECT operators, the Society Past clause must be specified at the finish of the statement and the results of the combined queries are sorted. The following instance returns all products that are red or xanthous and sorts this combined list past the cavalcade ListPrice.

              Utilize AdventureWorks2012;   Get   SELECT Name, Color, ListPrice   FROM Production.Product   WHERE Color = 'Red'   -- Society BY cannot be specified hither.   UNION ALL   SELECT Proper name, Color, ListPrice   FROM Production.Production   WHERE Color = 'Yellow'   Club Past ListPrice ASC;

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

The following case demonstrates ordering of a result ready by the numerical EmployeeKey cavalcade in ascending order.

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

The following example orders a upshot ready by the numerical EmployeeKey column in descending order.

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

The post-obit case orders a upshot set up past the LastName column.

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

The following example orders by two columns. This query first sorts in ascending lodge by the FirstName cavalcade, and so sorts mutual FirstName values in descending society by the LastName column.

              -- Uses AdventureWorks      SELECT EmployeeKey, FirstName, LastName FROM DimEmployee   WHERE LastName LIKE 'A%'   Social club BY LastName, FirstName;

See Also

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