ROW_NUMBER (Transact-SQL)

[アーティクル]
05/23/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイント Microsoft Fabric のウェアハウス

結果セットの出力に番号を設定します。具体的には、結果セットのパーティション内の行について、各パーティションの最初の行を 1 とした連続する数値を返します。

ROW_NUMBER と RANK は似ています。 ROW_NUMBER は、すべての行に順番に番号を付けます (たとえば 1、2、3、4、5)。 RANK は、同順位に対して同じ番号を付けます (たとえば 1、2、2、4、5)。

Note

ROW_NUMBER は、クエリの実行時に計算される一時的な値です。番号をテーブルに保持するには、「IDENTITY プロパティ」と「SEQUENCE」をご覧ください。

Transact-SQL 構文表記規則

構文

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

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

PARTITION BY value_expression
FROM 句で生成された結果セットを、ROW_NUMBER 関数が適用されるパーティションに分割します。 value_expression は、結果セットをパーティションに分割するときに使用する列を指定します。 PARTITION BY を指定しない場合、関数ではクエリ結果セットのすべての行を 1 つのグループとして扱います。詳細については、OVER 句 (Transact-SQL) に関する記事を参照してください。

order_by_clause
ORDER BY 句は、指定したパーティション内の行に一意の ROW_NUMBER を割り当てる順序を決定します。この引数は必須です。詳細については、OVER 句 (Transact-SQL) に関する記事を参照してください。

戻り値の型

bigint

全般的な解説

以下の条件が満たされている場合を除き、ROW_NUMBER() を使用したクエリによって返される行が、実行ごとにまったく同じ順序になるという保証はありません。

パーティション分割された行の値が一意である。
ORDER BY 列の値が一意である。
パーティション分割された列と ORDER BY 列の値の組み合わせが一意である。

ROW_NUMBER() は非決定的です。詳細については、「決定的関数と非決定的関数」を参照してください。

例

A. 簡単な例

次のクエリは、4 つのシステムテーブルをアルファベット順に返します。

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

結果セットは次のようになります。

name	recovery_model_desc
master	SIMPLE
model	FULL
msdb	SIMPLE
tempdb	SIMPLE

各行の前に行番号列を追加するには、ROW_NUMBER 関数で列 (ここでは Row# という名前) を追加します。 ORDER BY 句を OVER 句まで移動する必要があります。

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

結果セットは次のようになります。

Row#	name	recovery_model_desc
1	master	SIMPLE
2	model	FULL
3	msdb	SIMPLE
4	tempdb	SIMPLE

recovery_model_desc 列に PARTITION BY 句を追加すると、recovery_model_desc 値が変更されたときに番号付けが再開されます。

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;

結果セットは次のようになります。

Row#	name	recovery_model_desc
1	model	FULL
1	master	SIMPLE
2	msdb	SIMPLE
3	tempdb	SIMPLE

B. 販売員の行番号を返す

次の例では、Adventure Works Cycles の販売員について、今年に入ってからの売り上げ順位に基づく行番号を返します。

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

C. 行のサブセットを返す

次の例では、SalesOrderHeader テーブル内のすべての行の行番号を OrderDate の順序で計算し、50 から 60 までの行のみを返します。

USE AdventureWorks2022;  
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. ROW_NUMBER() を PARTITION と共に使用する

次の例では、PARTITION BY 引数を使用して、列 TerritoryName を基準にクエリ結果セットをパーティションに分割します。 ORDER BY 句に指定した OVER 句によって、列 SalesYTD を基準に各パーティション内の行の順序付けが行われます。 ORDER BY ステートメントの SELECT 句によって、TerritoryName を基準にクエリ結果セット全体の順序付けが行われます。

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

例: Azure Synapse Analytics、Analytics Platform System (PDW)

E. 販売員の行番号を返す

次の例は、割り当てられている販売ノルマに基づいて営業担当者の ROW_NUMBERを返します。

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

F. ROW_NUMBER() を PARTITION と共に使用する

次の例では、ROW_NUMBER 関数を PARTITION BY 引数と共に使用します。これにより、ROW_NUMBER 関数は各パーティション内の行に番号を付けます。

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

参照

RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL)

ROW_NUMBER (Transact-SQL)

構文

引数

戻り値の型

全般的な解説

例

A. 簡単な例

B. 販売員の行番号を返す

C. 行のサブセットを返す

D. ROW_NUMBER() を PARTITION と共に使用する

例: Azure Synapse Analytics、Analytics Platform System (PDW)

E. 販売員の行番号を返す

F. ROW_NUMBER() を PARTITION と共に使用する

参照

フィードバック

フィードバック

その他のリソース