データベース上のデータを抽出する際、ある一定の規則でソートして上位n件のデータを取得したい、という場合がある。今回は、Oracle上で、以前作成したUSER_DATAテーブル上で、IDの昇順にソートし上位5件を取得するSQLを作成してみたので、共有する。
前提条件
Oracle XEのインストールが完了し、以下のUSER_DATAテーブルが作成済であること。
また、USER_DATAテーブルに以下のデータが追加されていること。
1 | SELECT * FROM USER_DATA |
データを加工しソートし取得するSQL
前提条件で作成済のUSER_DATAテーブルのデータを、生年月日と性別を加工した上で、IDの昇順にソートするSQLの実行結果は、以下の通り。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 |
削除または保存していないWordドキュメントの復元方法【4DDiG Windowsデータ復元】ワード(Word)データ等のファイルを誤って削除してしまった場合は、通常はデータの復元ができませんが、4DDiGというソフトウェアを利用...
データを加工しソート後に上位5件を取得するSQL
USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。このときのソート処理は、副問い合わせの中で行う必要がある。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 |
「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...
ROW_NUMBER 分析関数を利用したSQL
先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用して実行した結果は、以下の通り。このとき、ROW_NUMBER分析関数内のORDER BY句で指定した順にデータがソートされることも確認できる。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 |
要点まとめ
- Oracle上でソートして上位n件のデータを取得するには、SQLの副問い合わせ内でソートした後でROWNUM関数で上位n件を取得するか、ROW_NUMBER 分析関数を利用しすればよい。
