SQL Server 入門 SQL Server 入門

ホーム > よく使われる SQL Server のビルトインのファンクション > よく使われる順位付け関数 1 - ROW_NUMBER

よく使われる順位付け関数 1 - ROW_NUMBER

よく使われる順位付け関数 1 - ROW_NUMBER

前回は 「 よく使われる日付と時刻の関数 2 - DATEADD, DATEDIFF, ISDATE 」 で、 よく使われる日付と時刻の組み込み文字列関数のうち、DATEADD, DATEDIFF, ISDATE を使ってみました。

次は、便利な順位付け関数を見ていきましょう。

こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。


ROW_NUMBER ファンクション

ROW_NUMBER ファンクションは、結果セットにシーケンス番号を振ってくれる関数です。 

構文は次の通りです。

ROW_NUMBER ( ) OVER ( [ PARTITION BY [パティションカラム 1 ], [パティションカラム 2], ... ] ORDER BY [ソートカラム 1], [ソートカラム 2], ...  )

パティションが指定されていない場合は結果セット全体を 1 から順番に、パティションを指定した場合は各パティション内で 1 から順番に、ORDER BY で指定した順番でシーケンス番号が振られます。

ROW_NUMBER ファンクションは、指定したカラムの値が同じレコードが複数あっても、同じ番号を返すことはありません。

ですので、結果セットに一時的に ID を振りたい時なんかにも便利で、よく使っています。


例えば、次のような Student テーブルがあります。

ROW_NUMBER ファンクション 1

誕生日の年が古い -> 新しい、男性 -> 女性という順に 1, 2, 3 ... とシーケンス番号を振りたい場合は次のように取得することができます。

SELECT  ROW_NUMBER() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RowNo,
        *
FROM    Student;

[ 実行結果 ]
ROW_NUMBER ファンクション 2

ORDER BY の後ろに、まず YEAR(Birthday) で誕生日の年の部分を取得して降順で、次に性別の降順でソートするように指定しています。

昇順 は ASC、降順は DESC で指定しますが、何も指定しなければ昇順になります。


青で囲まれた学生はどちらも同じ年生まれの女性です。 このように ORDER BY で指定しきれていないレコードの順序は、毎回同じになる保障はありません。

どちらでも良い場合はいいですが、ハッキリと取得したい順序が決まっている場合は、そこまで ORDER BY で指定したほうが良いと思います。


続いて、パティションと共に ROW_NUMBER を使ってみましょう。

次のような TestResult テーブルがあります。

ROW_NUMBER ファンクション 3

TestID ごとに、Score の高い人から順位を取得して、1 位の結果のみ取得してみましょう。

SELECT  *
FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY TestID 
                               ORDER BY     Score DESC) AS RowNo,
            * 
     FROM   TestResult) AS T
WHERE   RowNo = 1;

[ 実行結果 ]
ROW_NUMBER ファンクション 4

PARTITION BY で TestID カラムを指定して、Score の降順に番号を振り、その結果をサブクエリーとして使って RowNo が 1 のレコードのみを取得しています。


ちなみに、サブクエリーが返す結果は以下の通りです。

SELECT  ROW_NUMBER() OVER (PARTITION BY TestID 
                           ORDER BY     Score DESC) AS RowNo,
        * 
FROM    TestResult;

[ 実行結果 ]
ROW_NUMBER ファンクション 5


次は、順位付け関数の RANK と DENSE_RANK を使ってみましょう。

次へ よく使われる順位付け関数 2 - RANK, DENSE_RANK

SQL Server 関連の人気書籍
ホーム > よく使われる SQL Server のビルトインのファンクション > よく使われる順位付け関数 1 - ROW_NUMBER