Categories: Scripts

Show ROW NUMBERS in T-SQL RESULT set

Tested on: SQL Server 2016 Developer Edition
Accurate as of: January, 2018

In this query, I’ve added a record number column to the result set using OVER and ORDER BY clauses with the ROW_NUMBER function in T-SQL.

Note that the ORDER BY clause is required. The OVER() clause tells the SQL Engine to sort data on the specified column (in this case OrderId) and assign numbers as per the sort results.

SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
        GrandTotal, CustomerId, PurchaseDate
FROM Orders
 
 
--- Rick's Code Snippet Archive 
--- Snippet #D08DA59C43B3ECDBD2DF39E2BDDEF56B (TSQL) 
--- For the latest version of this code or to post a question or comment about it, visit:  
--- http://www.SevenDaysOfSchema.com/tsql-examples/show-row-numbers-in-result-set/

Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.

In this Script

ROW_NUMBER     SELECT     FROM    

ROW_NUMBER (Transact-SQL)

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

`ROW_NUMBER` and `RANK` are similar. `ROW_NUMBER` numbers all rows sequentially (for example 1, 2, 3, 4, 5). `RANK` provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

NOTE: `ROW_NUMBER` is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

/* 

 A. Simple examples

*/

SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;





/* 

 B. Returning the row number for salespeople

*/
  
USE AdventureWorks2012;   
GO  
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;  





/* 

 C. Returning a subset of rows

*/
  
USE AdventureWorks2012;  
GO  
WITH OrderedOrders AS  
(  
    SELECT SalesOrderID, OrderDate,  
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber  
    FROM Sales.SalesOrderHeader   
)   
SELECT SalesOrderID, OrderDate, RowNumber    
FROM OrderedOrders   
WHERE RowNumber BETWEEN 50 AND 60;  





/* 

 D. Using ROW_NUMBER() with PARTITION

*/
  
USE AdventureWorks2012;  
GO  
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;  





/* 

 E. Returning the row number for salespeople

*/
  
-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) 
    AS RowNumber,  
    FirstName, LastName,   
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName;  





/* 

 F. Using ROW_NUMBER() with PARTITION

*/
  
-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey 
        ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,  
    LastName, SalesTerritoryKey AS Territory,  
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName, SalesTerritoryKey;

Related Topics:   DENSE_RANK   NTILE   RANK

Ranking Functions:   DENSE_RANK   NTILE   RANK

TOP

SELECT Clause (Transact-SQL)

Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.

Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.

-- Syntax for SQL Server and Azure SQL Database  
  
&lgt;SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ &lgt;common_table_expression> [,...n] ] } ]  
    &lgt;query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ &lgt;FOR Clause>]   
    [ OPTION ( &lgt;query_hint> [ ,...n ] ) ]   
&lgt;query_expression> ::=   
    { &lgt;query_specification> | ( &lgt;query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        &lgt;query_specification> | ( &lgt;query_expression> ) [...n ] ]   
&lgt;query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    &lgt; select_list >   
    [ INTO new_table ]   
    [ FROM { &lgt;table_source> } [ ,...n ] ]   
    [ WHERE &lgt;search_condition> ]   
    [ &lgt;GROUP BY> ]   
    [ HAVING &lgt; search_condition > ]   
  
  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
[ WITH &lgt;common_table_expression> [ ,...n ] ]  
SELECT &lgt;select_criteria>  
[;]  
  
&lgt;select_criteria> ::=  
    [ TOP ( top_expression ) ]   
    [ ALL | DISTINCT ]   
    { * | column_name | expression } [ ,...n ]   
    [ FROM { table_source } [ ,...n ] ]  
    [ WHERE &lgt;search_condition> ]   
    [ GROUP BY &lgt;group_by_clause> ]   
    [ HAVING &lgt;search_condition> ]   
    [ ORDER BY &lgt;order_by_expression> ]  
    [ OPTION ( &lgt;query_option> [ ,...n ] ) ]

/* 

 A. Using SELECT to retrieve rows and columns

*/
  
SELECT *  
FROM DimEmployee  
ORDER BY LastName;  





/* 

 B. Using SELECT with column headings and calculations

*/
  
SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay  
FROM DimEmployee  
ORDER BY LastName;  





/* 

 C. Using DISTINCT with SELECT

*/
  
SELECT DISTINCT Title  
FROM DimEmployee  
ORDER BY Title;  





/* 

 D. Using GROUP BY

*/
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 E. Using GROUP BY with multiple groups

*/
  

SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey, PromotionKey  
ORDER BY OrderDateKey;   





/* 

 F. Using GROUP BY and WHERE

*/
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
WHERE OrderDateKey > '20020801'  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 G. Using GROUP BY with an expression

*/
  
SELECT SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY (OrderDateKey * 10);  





/* 

 H. Using GROUP BY with ORDER BY

*/
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  





/* 

 I. Using the HAVING clause

*/
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
HAVING OrderDateKey > 20010000  
ORDER BY OrderDateKey;

Related Topics:   Hints   SELECT Examples

T-SQL Query Elements:   CONTAINS   EXPLAIN   FREETEXT   FROM   GROUP BY   HAVING   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

TOP

FROM (Transact-SQL)

Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).

Permissions: Requires the permissions for the DELETE, SELECT, or UPDATE statement.

-- Syntax for SQL Server and Azure SQL Database  
  
[ FROM { &lgt;table_source> } [ ,...n ] ]   
&lgt;table_source> ::=   
{  
    table_or_view_name [ [ AS ] table_alias ]   
        [ &lgt;tablesample_clause> ]   
        [ WITH ( &lgt; table_hint > [ [ , ]...n ] ) ]   
    | rowset_function [ [ AS ] table_alias ]   
        [ ( bulk_column_alias [ ,...n ] ) ]   
    | user_defined_function [ [ AS ] table_alias ]  
    | OPENXML &lgt;openxml_clause>   
    | derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]   
    | &lgt;joined_table>   
    | &lgt;pivoted_table>   
    | &lgt;unpivoted_table>  
    | @variable [ [ AS ] table_alias ]  
    | @variable.function_call ( expression [ ,...n ] )   
        [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]  
    | FOR SYSTEM_TIME &lgt;system_time>   
}  
&lgt;tablesample_clause> ::=  
    TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )   
        [ REPEATABLE ( repeat_seed ) ]   
  
&lgt;joined_table> ::=   
{  
    &lgt;table_source> &lgt;join_type> &lgt;table_source> ON &lgt;search_condition>   
    | &lgt;table_source> CROSS JOIN &lgt;table_source>   
    | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] &lgt;joined_table> [ ) ]   
}  
&lgt;join_type> ::=   
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ &lgt;join_hint> ] ]  
    JOIN  
  
&lgt;pivoted_table> ::=  
    table_source PIVOT &lgt;pivot_clause> [ [ AS ] table_alias ]  
  
&lgt;pivot_clause> ::=  
        ( aggregate_function ( value_column [ [ , ]...n ])   
        FOR pivot_column   
        IN ( &lgt;column_list> )   
    )   
  
&lgt;unpivoted_table> ::=  
    table_source UNPIVOT &lgt;unpivot_clause> [ [ AS ] table_alias ]  
  
&lgt;unpivot_clause> ::=  
    ( value_column FOR pivot_column IN ( &lgt;column_list> ) )   
  
&lgt;column_list> ::=  
    column_name [ ,...n ]   
  
&lgt;system_time> ::=  
{  
       AS OF &lgt;date_time>  
    |  FROM &lgt;start_date_time> TO &lgt;end_date_time>  
    |  BETWEEN &lgt;start_date_time> AND &lgt;end_date_time>  
    |  CONTAINED IN (&lgt;start_date_time> , &lgt;end_date_time>)   
    |  ALL  
}  
  
    &lgt;date_time>::=  
        &lgt;date_time_literal> | @date_time_variable  
  
    &lgt;start_date_time>::=  
        &lgt;date_time_literal> | @date_time_variable  
  
    &lgt;end_date_time>::=  
        &lgt;date_time_literal> | @date_time_variable  
  
  
  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
FROM { &lgt;table_source> [ ,...n ] }  
  
&lgt;table_source> ::=   
{  
    [ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias  
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]  
    | &lgt;joined_table>  
}  
  
&lgt;joined_table> ::=   
{  
    &lgt;table_source> &lgt;join_type> &lgt;table_source> ON search_condition   
    | &lgt;table_source> CROSS JOIN &lgt;table_source> 
    | left_table_source { CROSS | OUTER } APPLY right_table_source   
    | [ ( ] &lgt;joined_table> [ ) ]   
}  
  
&lgt;join_type> ::=   
    [ INNER ] [ &lgt;join hint> ] JOIN  
    | LEFT  [ OUTER ] JOIN  
    | RIGHT [ OUTER ] JOIN  
    | FULL  [ OUTER ] JOIN  
  
&lgt;join_hint> ::=   
    REDUCE  
    | REPLICATE  
    | REDISTRIBUTE

/* 

 Arguments

*/

SELECT p.ProductID, v.BusinessEntityID  
FROM Production.Product AS p   
JOIN Purchasing.ProductVendor AS v  
ON (p.ProductID = v.ProductID);  
  





/* 

 A. Using a simple FROM clause

*/
    
SELECT TerritoryID, Name  
FROM Sales.SalesTerritory  
ORDER BY TerritoryID ;  





/* 

 B. Using the TABLOCK and HOLDLOCK optimizer hints

*/
    
BEGIN TRAN  
SELECT COUNT(*)   
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;  





/* 

 C. Using the SQL-92 CROSS JOIN syntax

*/
wql    
SELECT e.BusinessEntityID, d.Name AS Department  
FROM HumanResources.Employee AS e  
CROSS JOIN HumanResources.Department AS d  
ORDER BY e.BusinessEntityID, d.Name ;  





/* 

 D. Using the SQL-92 FULL OUTER JOIN syntax

*/
  
-- The OUTER keyword following the FULL keyword is optional.  
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
FULL OUTER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 E. Using the SQL-92 LEFT OUTER JOIN syntax

*/
    
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 F. Using the SQL-92 INNER JOIN syntax

*/
    
-- By default, SQL Server performs an INNER JOIN if only the JOIN   
-- keyword is specified.  
SELECT p.Name, sod.SalesOrderID  
FROM Production.Product AS p  
INNER JOIN Sales.SalesOrderDetail AS sod  
ON p.ProductID = sod.ProductID  
ORDER BY p.Name ;  





/* 

 G. Using the SQL-92 RIGHT OUTER JOIN syntax

*/
    
SELECT st.Name AS Territory, sp.BusinessEntityID  
FROM Sales.SalesTerritory AS st   
RIGHT OUTER JOIN Sales.SalesPerson AS sp  
ON st.TerritoryID = sp.TerritoryID ;  





/* 

 H. Using HASH and MERGE join hints

*/
    
SELECT p.Name AS ProductName, v.Name AS VendorName  
FROM Production.Product AS p   
INNER MERGE JOIN Purchasing.ProductVendor AS pv   
ON p.ProductID = pv.ProductID  
INNER HASH JOIN Purchasing.Vendor AS v  
ON pv.BusinessEntityID = v.BusinessEntityID  
ORDER BY p.Name, v.Name ;  





/* 

 I. Using a derived table

*/
    
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City  
FROM Person.Person AS p  
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID   
INNER JOIN  
   (SELECT bea.BusinessEntityID, a.City   
    FROM Person.Address AS a  
    INNER JOIN Person.BusinessEntityAddress AS bea  
    ON a.AddressID = bea.AddressID) AS d  
ON p.BusinessEntityID = d.BusinessEntityID  
ORDER BY p.LastName, p.FirstName;  





/* 

 J. Using TABLESAMPLE to read data from a sample of rows in a table

*/
    
SELECT *  
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;  





/* 

 K. Using APPLY

*/

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary  
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;  





/* 

 L. Using CROSS APPLY

*/

USE master;  
GO  
SELECT dbid, object_id, query_plan   
FROM sys.dm_exec_cached_plans AS cp   
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);   
GO  





/* 

 M. Using FOR SYSTEM_TIME

*/

SELECT DepartmentNumber,   
    DepartmentName,   
    ManagerID,   
    ParentDepartmentNumber   
FROM DEPARTMENT  
FOR SYSTEM_TIME AS OF '2014-01-01'  
WHERE ManagerID = 5;





/* 

 N. Using the INNER JOIN syntax

*/

-- Uses AdventureWorks  
  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis 
INNER JOIN DimProduct AS dp  
    ON dp.ProductKey = fis.ProductKey;  





/* 

 O. Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax

*/

-- Uses AdventureWorks  
  
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
FROM FactInternetSales AS fis 
LEFT OUTER JOIN DimProduct AS dp  
    ON dp.ProductKey = fis.ProductKey;  





/* 

 P. Using the FULL OUTER JOIN syntax

*/

-- Uses AdventureWorks  
  
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst 
FULL OUTER JOIN FactInternetSales AS fis  
    ON dst.SalesTerritoryKey = fis.SalesTerritoryKey  
ORDER BY fis.SalesOrderNumber;  





/* 

 Q. Using the CROSS JOIN syntax

*/

-- Uses AdventureWorks  
  
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber  
FROM DimSalesTerritory AS dst 
CROSS JOIN FactInternetSales AS fis  
ORDER BY fis.SalesOrderNumber;  





/* 

 R. Using a derived table

*/

-- Uses AdventureWorks  
  
SELECT CustomerKey, LastName  
FROM  
   (SELECT * FROM DimCustomer  
    WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable  
WHERE LastName = 'Smith'  
ORDER BY LastName;  





/* 

 S. REDUCE join hint example

*/

-- Uses AdventureWorks  
  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REDUCE JOIN FactInternetSales AS fis   
          ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;  





/* 

 T. REPLICATE join hint example

*/

-- Uses AdventureWorks  
  
EXPLAIN SELECT SalesOrderNumber  
FROM  
   (SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName  
    FROM DimProduct AS dp   
      INNER REPLICATE JOIN FactInternetSales AS fis  
          ON dp.ProductKey = fis.ProductKey  
   ) AS dTable  
ORDER BY SalesOrderNumber;  





/* 

 U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join

*/

-- Uses AdventureWorks  
  
EXPLAIN  
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost  
FROM DimProduct AS dp 
INNER REDISTRIBUTE JOIN FactInternetSales AS fis  
    ON dp.ProductKey = fis.ProductKey;

Related Topics:   CONTAINSTABLE   DELETE   FREETEXTTABLE   INSERT   OPENQUERY   OPENROWSET   Operators   UPDATE   WHERE

T-SQL Query Elements:   CONTAINS   EXPLAIN   FREETEXT   GROUP BY   HAVING   IS NULL   PIVOT and UNPIVOT   PREDICT   READTEXT   TOP   UPDATE   UPDATETEXT   WHERE   WRITETEXT

About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.

Rick Bishop

Recent Posts

C# System.Uri Class Examples

If you've needed to parse or construct a URI in C#, you've likely done it…

6 years ago

C# Basics – Access Modifiers

The second installment in my series of C# basics illustrates the proper use of access…

6 years ago

C# Coding Style

This page details the coding style I've adopted for C# code in my applications. You…

6 years ago

C# Basics – Inheritance

For the new C# section of my website, I wanted to post some notes that…

6 years ago

5 Reasons to Lock Down Your LinkedIn Profile

There are some pretty compelling reasons to lock down your LinkedIn account now. We bet…

6 years ago

LinkedIn is Ignoring Your Privacy Settings and You Paid Them to Do It

We bet you didn't know that your full name, picture, work history, and more may…

6 years ago