DB

Oracle上でソートして上位n件のデータを取得してみた

 

データベース上のデータを抽出する際、ある一定の規則でソートして上位n件のデータを取得したい、という場合がある。今回は、Oracle上で、以前作成したUSER_DATAテーブル上で、IDの昇順にソートし上位5件を取得するSQLを作成してみたので、共有する。

前提条件

Oracle XEのインストールが完了し、以下のUSER_DATAテーブルが作成済であること。
前提条件_1

また、USER_DATAテーブルに以下のデータが追加されていること。

SELECT * FROM USER_DATA
前提条件_2

データを加工しソートし取得するSQL

前提条件で作成済のUSER_DATAテーブルのデータを、生年月日と性別を加工した上で、IDの昇順にソートするSQLの実行結果は、以下の通り。

SELECT 
    ID, 
    NAME, 
    TO_DATE(TO_CHAR(BIRTH_YEAR, 'FM0000') 
         || TO_CHAR(BIRTH_MONTH, 'FM00') 
         || TO_CHAR(BIRTH_DAY, 'FM00'), 'YYYYMMDD') 
    AS BIRTHDAY,
    CASE SEX 
        WHEN '1' THEN '男'
        WHEN '2' THEN '女'
        ELSE ''
    END AS SEX_STR,
    MEMO 
FROM USER_DATA
ORDER BY ID ASC
データを加工しソートし取得するSQL実行結果
削除または保存していないWordドキュメントの復元方法【4DDiG Windowsデータ復元】ワード(Word)データ等のファイルを誤って削除してしまった場合は、通常はデータの復元ができませんが、4DDiGというソフトウェアを利用...

データを加工しソート後に上位5件を取得するSQL

USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。このときのソート処理は、副問い合わせの中で行う必要がある。

SELECT usr.* 
FROM (
    SELECT 
        ID, 
        NAME, 
        TO_DATE(TO_CHAR(BIRTH_YEAR, 'FM0000') 
             || TO_CHAR(BIRTH_MONTH, 'FM00') 
             || TO_CHAR(BIRTH_DAY, 'FM00'), 'YYYYMMDD') 
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO 
    FROM USER_DATA
    ORDER BY ID ASC
) usr
WHERE ROWNUM <= 5
データを加工しソート後に上位5件を取得するSQL実行結果



「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...

ROW_NUMBER 分析関数を利用したSQL

先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用して実行した結果は、以下の通り。このとき、ROW_NUMBER分析関数内のORDER BY句で指定した順にデータがソートされることも確認できる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        TO_DATE(TO_CHAR(BIRTH_YEAR, 'FM0000') 
             || TO_CHAR(BIRTH_MONTH, 'FM00') 
             || TO_CHAR(BIRTH_DAY, 'FM00'), 'YYYYMMDD') 
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5
ROW_NUMBER 分析関数を利用したSQL実行結果

要点まとめ

  • Oracle上でソートして上位n件のデータを取得するには、SQLの副問い合わせ内でソートした後でROWNUM関数で上位n件を取得するか、ROW_NUMBER 分析関数を利用しすればよい。
RELATED POST