SQL - Row_Number()

ROW_NUMBER

ROW_NUMBER ( )  

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

To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row#. You must move the ORDER BY clause up to the OVER clause.

SELECT

  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,

  name, recovery_model_desc

FROM sys.databases

WHERE database_id < 5;

Adding a PARTITION BY clause on the recovery_model_desc column, will restart the numbering when the recovery_model_desc value changes.

SELECT

  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC)

    AS Row#,

  name, recovery_model_desc

FROM sys.databases WHERE database_id < 5;

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; 

Row FirstName    LastName               SalesYTD 

--- -----------  ---------------------- ----------------- 

1   Linda        Mitchell               4251368.54 

2   Jae          Pak                    4116871.22 

3   Michael      Blythe                 3763178.17 

4   Jillian      Carson                 3189418.36 

5   Ranjit       Varkey Chudukatil      3121616.32 

6   José         Saraiva                2604540.71 

7   Shu          Ito                    2458535.61 

8   Tsvi         Reiter                 2315185.61 

9   Rachel       Valdez                 1827066.71 

10  Tete         Mensa-Annan            1576562.19 

11  David        Campbell               1573012.93 

12  Garrett      Vargas                 1453719.46 

13  Lynn         Tsoflias               1421810.92 

14  Pamela       Ansman-Wolfe           1352577.13 

Returning A Subset Of Rows

Calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.

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; 

Using ROW_NUMBER() with PARTITION

uses the PARTITION BY argument to partition the query result set by the column TerritoryName. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. The ORDER BY clause in the SELECT statement orders the entire query result set by TerritoryName.

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; 

FirstName  LastName             TerritoryName        SalesYTD      Row 

---------  -------------------- ------------------   ------------  --- 

Lynn       Tsoflias             Australia            1421810.92    1 

José       Saraiva              Canada               2604540.71    1 

Garrett    Vargas               Canada               1453719.46    2 

Jillian    Carson               Central              3189418.36    1 

Ranjit     Varkey Chudukatil    France               3121616.32    1 

Rachel     Valdez               Germany              1827066.71    1 

Michael    Blythe               Northeast            3763178.17    1 

Tete       Mensa-Annan          Northwest            1576562.19    1 

David      Campbell             Northwest            1573012.93    2 

Pamela     Ansman-Wolfe         Northwest            1352577.13    3 

Tsvi       Reiter               Southeast            2315185.61    1 

Linda      Mitchell             Southwest            4251368.54    1 

Shu        Ito                  Southwest            2458535.61    2 

Jae        Pak                  United Kingdom       4116871.22    1 

Returning The Row Number For Salespeople

returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

-- 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;

RowNumber  FirstName  LastName            SalesQuota 

---------  ---------  ------------------  ------------- 

1          Jillian    Carson              12,198,000.00 

2          Linda      Mitchell            11,786,000.00 

3          Michael    Blythe              11,162,000.00 

4          Jae        Pak                 10,514,000.00 

Using ROW_NUMBER() with PARTITION

shows using the ROW_NUMBER function with the PARTITION BY argument. This causes the ROW_NUMBER function to number the rows in each 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; 

RowNumber  LastName            Territory  SalesQuota 

---------  ------------------  ---------  ------------- 

1          Campbell            1           4,025,000.00 

2          Ansman-Wolfe        1           3,551,000.00 

3          Mensa-Annan         1           2,275,000.00 

1          Blythe              2          11,162,000.00 

1          Carson              3          12,198,000.00 

1          Mitchell            4          11,786,000.00 

2          Ito                 4           7,804,000.00