SELECT - ORDER BY clause (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

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 isn't guaranteed unless an ORDER BY clause is specified.
Determine the order in which ranking function values are applied to the result set.

Transact-SQL syntax conventions

Note

ORDER BY isn't supported in SELECT/INTO or CREATE TABLE AS SELECT (CTAS) statements in Azure Synapse Analytics or Analytics Platform System (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> ::=
{
    OFFSET { 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 Analytics Platform System (PDW):

[ 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. You can specify a sort column as a name or column alias, or as a non-negative integer representing the position of the column in the select list.

You can specify multiple sort columns. Column names must be unique. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. The result set is sorted by the first column, and then that ordered list is sorted by the second column, and so on.

The column names you reference in the ORDER BY clause must correspond to either a column or column alias 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, use the column alias on its own. Don't use the column alias as a part of an expression in the ORDER BY clause.

For example:

Correct usage:

SELECT SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
ORDER BY SchemaName; -- correct

Incorrect usage:

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

COLLATE collation_name

Specifies that the ORDER BY operation should be performed according to the collation you specify in collation_name, and not according to the collation of the column as defined in the table or view. The collation_name can be either a Windows collation name or a SQL collation name. For more information, see Collation and Unicode support. 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. NULL values are treated as the lowest possible values.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

Applies to: SQL Server 2012 (11.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

Specifies the number of rows to skip before the query starts to return rows from the query expression. The value can be an integer constant or expression that's greater than or equal to zero.

offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When you use a subquery, it can't reference any columns defined in the outer query scope. That is, it can't 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 appears in the Offset attribute of the TOP query operator.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

Applies to: SQL Server 2012 (11.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that's greater than or equal to one.

fetch_row_count_expression can be a variable, parameter, or constant scalar subquery. When you use a subquery, it can't reference any columns defined in the outer query scope. That is, it can't be 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 appears in the Rows or Top attribute of the TOP query operator.

Best practices

Avoid specifying integers in the ORDER BY clause as positional representations of the columns in the select list. For example, although a statement such as SELECT ProductID, Name FROM Production.Production ORDER BY 2 is valid, users might find it hard to understand compared with specifying the actual column name. In addition, changes to the select list, such as changing the column order or adding new columns, require modifying the ORDER BY clause to avoid unexpected results.

In a SELECT TOP (<n>) statement, always use an ORDER BY clause. This is the only way to predictably indicate which rows TOP affects. For more information, see TOP.

Interoperability

When you use ORDER BY with a SELECT...INTO or INSERT...SELECT statement to insert rows from another source, the ORDER BY clause doesn't guarantee the rows are inserted in the specified order.

Using OFFSET and FETCH in a view doesn't change the updateability property of the view.

Limitations

There's no limit to the number of columns in the ORDER BY clause. However, the total size of the columns specified in an ORDER BY clause can't exceed 8,060 bytes.

You can't use columns of type ntext, text, image, geography, geometry, or xml in an ORDER BY clause.

You can't specify an integer or constant when order_by_expression appears in a ranking function. For more information, see SELECT - OVER clause.

If you alias a table name in the FROM clause, you must use the alias name to qualify its columns in the ORDER BY clause.

If the SELECT statement contains one of the following clauses or operators, you must define column names and aliases specified in the ORDER BY clause in the select list:

UNION operator
EXCEPT operator
INTERSECT operator
SELECT DISTINCT

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

In a query that uses UNION, EXCEPT, or INTERSECT operators, you can use ORDER BY only at the end of the statement. This restriction applies only to when you specify UNION, EXCEPT, and INTERSECT in a top-level query and not in a subquery. See the Examples section.

The ORDER BY clause isn't valid in views, inline functions, derived tables, and subqueries, unless you also specify either the TOP or OFFSET and FETCH clauses. When you use ORDER BY in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause doesn't guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

OFFSET and FETCH aren't supported in indexed views or in a view that is defined by using the CHECK OPTION clause.

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

The OVER clause doesn't support OFFSET and FETCH.
You can't specify OFFSET and FETCH directly in INSERT, UPDATE, MERGE, and DELETE statements, but you can specify them in a subquery defined in these statements. For example, in the INSERT INTO SELECT statement, you can specify OFFSET and FETCH in the SELECT statement.
In a query that uses UNION, EXCEPT or INTERSECT operators, only the final query that specifies the order of the query results can specify OFFSET and FETCH.
You can't combine TOP with OFFSET and FETCH in the same query expression (in the same query scope).

Use OFFSET and FETCH to limit the rows returned

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 OFFSET and FETCH as a paging solution requires running the query one time for each page of data returned to the client application. For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20, and so on. Each query is independent and not related to each other in any way. This condition means 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 state. To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met:

The underlying data that the query uses doesn't change. That is, either the query doesn't update the rows, or all requests for pages from the query execute in a single transaction using either snapshot or serializable transaction isolation. For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL.
The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

See the example Run multiple queries in a single transaction in the Examples section.

If consistent execution plans are important in your paging solution, consider using the OPTIMIZE FOR query hint for the OFFSET and FETCH parameters. See Specify expressions for OFFSET and FETCH values in the Examples section. For more information about OPTIMIZE FOR, see Query hints.

Examples

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

Category	Featured syntax elements
Basic syntax	`ORDER BY`
Specify ascending and descending order	`DESC` or `ASC`
Specify a collation	`COLLATE`
Specify a conditional order	`CASE` expression
Use ORDER BY in a ranking function	Ranking functions
Limit the number of rows returned	`OFFSET` and `FETCH`
Use ORDER BY with UNION, EXCEPT, and INTERSECT	`UNION`

Basic syntax

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

A. Specify a single column defined in the select list

The following example orders the result set by the numeric ProductID column. Because you don't specify a sort order, the query uses the default ascending order.

USE AdventureWorks2025;
GO

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

B. Specify a column that isn't defined in the select list

The following example orders the result set by a column that the select list doesn't include, but the FROM clause specifies the table that contains the column.

USE AdventureWorks2025;
GO

SELECT ProductID,
       Name,
       Color
FROM Production.Product
ORDER BY ListPrice;

C. Specify an alias as the sort column

The following example specifies the column alias SchemaName as the sort order column.

USE AdventureWorks2025;
GO

SELECT name,
       SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
WHERE type = 'U'
ORDER BY SchemaName;

D. Specify an expression as the sort column

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

USE AdventureWorks2025;
GO

SELECT BusinessEntityID,
       JobTitle,
       HireDate
FROM HumanResources.Employee
ORDER BY DATEPART(year, HireDate);

Specify ascending and descending sort order

A. Specify a descending order

The following example orders the result set by the numeric column ProductID in descending order.

USE AdventureWorks2025;
GO

SELECT ProductID,
       Name
FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID DESC;

B. Specify an ascending order

The following example orders the result set by the Name column in ascending order. The characters are sorted alphabetically, not numerically. That is, 10 sorts before 2.

USE AdventureWorks2025;
GO

SELECT ProductID,
       Name
FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY Name ASC;

C. Specify both ascending and descending order

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

USE AdventureWorks2025;
GO

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

Specify a collation

The following example shows how specifying a collation in the ORDER BY clause can change the order in which the query results are returned. A table is created that contains a column defined by using a case-insensitive, accent-insensitive collation. Values are inserted with various case and accent differences. Because a collation isn't specified in the ORDER BY clause, the first query uses the collation of the column when sorting the values. In the second query, a case-sensitive, accent-sensitive collation is specified in the ORDER BY clause, which changes the order in which the rows are returned.

USE tempdb;
GO

CREATE TABLE #t1
(
    name NVARCHAR (15) COLLATE Latin1_General_CI_AI
);

INSERT INTO #t1
VALUES (N'Sánchez'),
       (N'Sanchez'),
       (N'sánchez'),
       (N'sanchez');

-- This query uses the collation specified for the column 'name' for sorting.
SELECT name
FROM #t1
ORDER BY name;

-- This query uses the collation specified in the ORDER BY clause for sorting.
SELECT name
FROM #t1
ORDER BY name COLLATE Latin1_General_CS_AS;

Specify a conditional order

The following examples use the CASE expression in an ORDER BY clause to conditionally determine the sort order of the rows based on a given column value. In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that have the SalariedFlag set to 1 are returned in order by the BusinessEntityID in descending order. Employees that have the SalariedFlag set to 0 are returned in order by the BusinessEntityID in ascending order. In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

SELECT BusinessEntityID,
       SalariedFlag
FROM HumanResources.Employee
ORDER BY
    CASE SalariedFlag
        WHEN 1 THEN BusinessEntityID
    END DESC,
    CASE
        WHEN SalariedFlag = 0 THEN BusinessEntityID
    END;

SELECT BusinessEntityID,
       LastName,
       TerritoryName,
       CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY
    CASE CountryRegionName
        WHEN 'United States' THEN TerritoryName ELSE CountryRegionName
    END;

Use ORDER BY in a ranking function

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

USE AdventureWorks2025;
GO

SELECT p.FirstName,
       p.LastName,
       ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number",
       RANK() OVER (ORDER BY a.PostalCode) AS "Rank",
       DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank",
       NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile",
       s.SalesYTD,
       a.PostalCode
FROM Sales.SalesPerson AS s
     INNER JOIN Person.Person AS p
         ON s.BusinessEntityID = p.BusinessEntityID
     INNER JOIN Person.Address AS a
         ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
      AND SalesYTD <> 0;

Limit the number of rows returned

Applies to: SQL Server 2012 (11.x) and later versions, Azure SQL Database, and Azure SQL Managed Instance.

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

A. Specify integer constants for OFFSET and FETCH values

The following example specifies an integer constant as the value for the OFFSET and FETCH clauses. The first 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 next query uses the clause OFFSET 5 ROWS to skip the first five rows and return all remaining rows. The final query uses the clause OFFSET 0 ROWS to start with the first row and then uses FETCH NEXT 10 ROWS ONLY to limit the rows returned to 10 rows from the sorted result set.

USE AdventureWorks2025;
GO

-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID,
       Name,
       GroupName
FROM HumanResources.Department
ORDER BY 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 OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID,
       Name,
       GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

B. Specify variables for OFFSET and FETCH values

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

USE AdventureWorks2025;
GO

-- Specifying variables for OFFSET and FETCH values
DECLARE @RowsToSkip AS TINYINT = 2,
        @FetchRows AS TINYINT = 8;

SELECT DepartmentID,
       Name,
       GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC OFFSET @RowsToSkip ROWS
FETCH NEXT @FetchRows ROWS ONLY;

C. Specify expressions for OFFSET and FETCH values

The following example uses the expression @StartingRowNumber - 1 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. Use this hint 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 not during query execution. For more information, see Query hints.

USE AdventureWorks2025;
GO

-- Specifying expressions for OFFSET and FETCH values
DECLARE @StartingRowNumber AS TINYINT = 1,
        @EndingRowNumber AS TINYINT = 8;

SELECT DepartmentID,
       Name,
       GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC OFFSET @StartingRowNumber - 1 ROWS
FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY
OPTION (OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20));

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

The following 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 table dbo.AppSettings.

-- Specifying a constant scalar subquery
USE AdventureWorks2025;
GO

CREATE TABLE dbo.AppSettings
(
    AppSettingID INT NOT NULL,
    PageSize INT NOT NULL
);

INSERT INTO dbo.AppSettings
VALUES (1, 10);

DECLARE @StartingRowNumber AS TINYINT = 1;

SELECT DepartmentID,
       Name,
       GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC
OFFSET @StartingRowNumber ROWS
FETCH NEXT (SELECT PageSize
            FROM dbo.AppSettings
            WHERE AppSettingID = 1) ROWS ONLY;

E. Run 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 BY clause ensures column uniqueness.

USE AdventureWorks2025;
GO

-- Ensure the database can support the snapshot isolation level set for the query.
IF (SELECT snapshot_isolation_state
    FROM sys.databases
    WHERE name = N'AdventureWorks2025') = 0
ALTER DATABASE AdventureWorks2025
SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Set the transaction isolation level to SNAPSHOT for this query.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

-- Beginning the transaction.
BEGIN TRANSACTION;

-- Declare and set the variables for the OFFSET and FETCH values.
DECLARE @StartingRowNumber AS INT = 1,
        @RowCountPerPage AS INT = 3;

-- Create the condition to stop the transaction after all rows have been returned.
WHILE (SELECT COUNT(*)
       FROM HumanResources.Department) >= @StartingRowNumber
    BEGIN
         -- 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 NEXT @RowCountPerPage ROWS ONLY;

        -- Increment @StartingRowNumber value.
        SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;
        CONTINUE;
    END
COMMIT TRANSACTION;

Use ORDER BY with UNION, EXCEPT, and INTERSECT

When a query uses the UNION, EXCEPT, or INTERSECT operators, specify the ORDER BY clause at the end of the statement. The query sorts the results of the combined queries. The following example returns all products that are red or yellow and sorts this combined list by the column ListPrice.

USE AdventureWorks2025;
GO

SELECT Name,
       Color,
       ListPrice
FROM Production.Product
WHERE Color = 'Red' -- ORDER BY cannot be specified here.
UNION ALL
SELECT Name,
       Color,
       ListPrice
FROM Production.Product
WHERE Color = 'Yellow'
ORDER BY ListPrice ASC;

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

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

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

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

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

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

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

The following example orders by two columns. This query first sorts in ascending order by the FirstName column, and then sorts common FirstName values in descending order by the LastName column.

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

Feedback

Was this page helpful?

Last updated on 2026-02-04

Share via

SELECT - ORDER BY clause (Transact-SQL)

Syntax

Arguments

order_by_expression

COLLATE collation_name

ASC | DESC

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

Best practices

Interoperability

Limitations

Use OFFSET and FETCH to limit the rows returned

Examples

Basic syntax

A. Specify a single column defined in the select list

B. Specify a column that isn't defined in the select list

C. Specify an alias as the sort column

D. Specify an expression as the sort column

Specify ascending and descending sort order

A. Specify a descending order

B. Specify an ascending order

C. Specify both ascending and descending order

Specify a collation

Specify a conditional order

Use ORDER BY in a ranking function

Limit the number of rows returned

A. Specify integer constants for OFFSET and FETCH values

B. Specify variables for OFFSET and FETCH values

C. Specify expressions for OFFSET and FETCH values

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

E. Run multiple queries in a single transaction

Use ORDER BY with UNION, EXCEPT, and INTERSECT

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

Related content

Feedback

Additional resources