ORDER BYでレコードの昇順・降順を決める

覚えるべきキーワード
・ASC(昇順)
・DESC(降順)
・NULLS FIRST
・NULLS LAST
・複数列のORDER BY
・FETCH FIRST(行数|割合 PERCENT) ROWS (ONLY | WITH TIES)
・OFFSET

順番を決めてレコードを取得したい時はORDER BYを使います。ORDER BYに順番にしたい列を指定して、その列の昇順または降順でレコードを取得します。分かっていると思いますが昇順は小さい順、降順は大きい順になります。

レコードに1、2、3の数値レコードが入っていたら昇順なら、1、2、3の順番、降順なら3、2、1の順番にレコードを取得します。数値レコードはイメージしやすいと思います。
では文字型のレコードはいかがでしょうか。何を基準に大きさを決めるのでしょうか。Aが大きいかBが大きいか分かる人いますか。もし知っていたら僕が教えてほしいです。文字型の場合はアルファベット順で降順、昇順を決めます。例えば、SASAKI、SAKURA、ISHIIという文字列があったら、昇順ならISHI、SAKURA 、SASAKI、降順なら昇順ならSASAKI 、SAKURA ISHIの順番になります。日付の場合は昇順は日付の古い順、降順は日付の新しい順になります。そのため12-Apr-98、25-May-02の日付レコードが入っていたら昇順なら12-Apr-98、25-May-02、01-Aug-10となり、降順なら01-Aug-10、25-May-02、12-Apr-98となります。ちなみにNULL値が入っていた場合ですが、NULLはどの値よりも大きな値、つまり最大値と定義されています。
となると昇順、降順にした場合にNULL値はどこで表示されるかは、、、分かりますよね。

このサンプルテーブルを使って以下のSQLを解説します。

サンプルテーブル1

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
10 SATOU 01-Aug-10
11 12-Apr-98
12 YAMADA
ISHII 9-Jan-00
13 YASUDA 20-Mar-11

ASC(数値)

ORDER BYにASCを指定すれば昇順でレコードが取得できます。昇順=小さい値から大きい値へ昇っていく順番です。
まずは数値型の列を昇順に並べるときの書き方です。

ASC(数値)
SELECT id,name.bd FROM employee ORDER BY id ASC;

employeeテーブルからidの昇順でレコードを取得します。

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
10 SATOU 01-Aug-10
11 12-Apr-98
12 YAMADA
13 YASUDA 20-Mar-11
ISHII 9-Jan-00

idには、10、11、12、null、13のレコードがあり、この昇順なので10、11、12、13、nullの順番にレコードを取得します。nullは最大値なので、最後になりますね。

ちなみに、ここではORDER BY id ASC;と記述していますが、ORDER BY 1 ASC;でも同じ結果になります。実はテーブルの列には番号が付与されています。SELECTでテーブルを表示したときに一番左の列を1として2,3、、、と順に番号が付いていますので、employee表は1番左がidなので、idは1でnameが2、bdが3になります。ORDER BY 2 ASC;
と書けばnameの昇順になります。

ASC(文字型)

文字型を昇順で並び替えるSQLです。nameは文字型の列になります。ちなみに文字型は正確に言えば、アルファベット順ではなく、文字コード順なので、’Japan’と’asia’の文字列があった時はJの方が文字コードが若いので、昇順では’Japan’と’asia’の順番になります。
大文字、小文字の順でさらにアルファベット順が正しい言い方です。

ASC(文字型)
SELECT id,name.bd FROM employee ORDER BY name ASC;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
10 SATOU 01-Aug-10
12 YAMADA
13 YASUDA 20-Mar-11
11 12-Apr-98

アルファベットの早い順なのでISHIDAからになります。そして、nullはやっぱり最後です。

ASC(日付)

日付の昇順は日付の古い順(早い順ともいう)から表示します。bdは日付型です。

ASC(日付)
SELECT id,name.bd FROM employee ORDER BY bd ASC;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
11 12-Apr-98
ISHII 9-Jan-00
10 SATOU 01-Aug-10
13 YASUDA 20-Mar-11
12 YAMADA

一番早い12-Apr-98が最初になります。これは1998年4月12日を意味しています。98は2098じゃないの?と思う方は日付書式に注目してください。DD-MON-RRの書式を使っていますので、98は1998年になります。ちゃんと古い順に並んでいて、最後は最も新しい(大きい)値のnullになります。

DESC(数値)

次は降順です。数値の降順は大きい値から並べます。降順=大きい値から小さい値へ降りていく順番です。

DESC(数値)
SELECT id,name,bd FROM employee ORDER BY id DESC;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
13 YASUDA 20-Mar-11
12 YAMADA
11 12-Apr-98
10 SATOU 01-Aug-10

数値の大きい方から表示するので、nullが最初にきて、13,12…という具合に降りていきます。

DESC(文字型)

文字型のレコードを降順で表示すると小文字のzからスタートとなります。ASC(文字型)でも書きましたが、正確には文字型の昇順・降順は文字コードの順番になります。

DESC(文字型)
SELECT id,name,bd FROM employee ORDER BY name DESC;

name列を降順に並べて表示します。

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
11 12-Apr-98
13 YASUDA 20-Mar-11
12 YAMADA
10 SATOU 01-Aug-10
ISHII 9-Jan-00

最大値のnullの後はアルファベットの逆順で表示されます。まあこのあたりは簡単ですよね。

DESC(日付)

日付型のレコードを降順で表示します。bdは日付型です。

DESC(日付)
SELECT id,name,bd FROM employee ORDER BY bd DESC;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
12 YAMADA
13 YASUDA 20-Mar-11
10 SATOU 01-Aug-10
ISHII 9-Jan-00
11 12-Apr-98

降順なので日付が新しい順に表示されます。そして降順の場合、nullはいつも最初に表示されます。

NULLS FIRST

NULLは最大値なので昇順の場合は最後、降順の場合は最初に表示されるのが普通ですが、nullだけは特別に指定して最初と最後をコントロールする記述方法があります。それがNULLS FIRSTとNULLS LASTです。

NULLS FIRST
SELECT id,name,bd FROM employee ORDER BY id ASC NULLS FIRST;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
10 SATOU 01-Aug-10
11 12-Apr-98
12 YAMADA
13 YASUDA 20-Mar-11

昇順だとnullは最後に来ますが、NULLS FIRSTを使うことでnull値のレコードを最初に持ってきて、後は昇順に表示することが可能になります。

NULLS LAST

NULLS FIRSTとくれば、次はNULLS LASTです。考え方は同じでNULLを最後に表示させたい時にNULLS LASTを指定します。

NULLS LAST
SELECT id,name,bd FROM employee ORDER BY name DESC NULLS LAST;

name列を降順に並べて表示します。

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
13 YASUDA 20-Mar-11
12 YAMADA
10 SATOU 01-Aug-10
ISHII 9-Jan-00
11 12-Apr-98

DESCだとNULLは最大値なので最初に表示しますが、NULLS LAST指定により最後に表示しています。

このように、NULL値に限っては最初と最後に指定して表示ができます。

複数列を指定したORDER BY

ORDER BYには複数列を指定することもできます。このサンプルテーブルを使って以下のSQLを解説します。

サンプルテーブル2

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
10 YASUDA 01-Aug-10
11 SATOU 12-Apr-98
12 YAMADA
ISHII 9-Jan-00
13 SATOU 20-Mar-11

name列の昇順で、bd列の降順で表示してみましょう。ASCとDESCをそれぞれ指定します。

複数列を指定したORDER BY
SELECT id,name,bd FROM employee ORDER BY name ASC,bd DESC;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
13 SATOU 20-Mar-11
11 SATOU 12-Apr-98
12 YAMADA
10 YASUDA 01-Aug-10

結果はこのようになりました。まずはnameの昇順ですが、nameにSATOUが2つあります。nameの昇順だけでは判断できませんが、次にbdを降順で並び替えるので、日付の新しい20-Mar-11が最初に来るので、このような表示順になります。

FETCHとORDER BY

ORDER BYとセットで使われるFETCHについてです。FETCHを使うと、戻される行を制限することができます。例えば、「id列の昇順で上位5行のみを戻したい」などです。FETCH句を使うことで制限した行数、もしくは制限したパーセント分の行数のみ戻せれるようになります。
構文はFETCH FIRST {行数|割合 PERSENT } ROWS {ONLY | WITH TIES}です。まずは行数か、割合 PERSENTを決めます。次にONLYかWITH TIESかを決めます。この違いですが、例えば、「name列の昇順で上位5行のみを戻したい」場合、5行目と6行目と7行目のデータが3つともSATOUだったらどうでしょう。5行目だけ返すのか、7行目まで返すのか悩ましいですよね。これを指定するのがONLYとWITH TIESです、ONLYの場合はすっぱり5行目までしか返しません。WITH TIESは5行目と同じ値まで返すので7行目まで返します。どちらを指定するかで結果が変わります。
あと、重要ではないのですが、構文にあるFIRSTはNEXTと書いても良いですし、ROWSは、ROWでも良いです。

では実際のSQLを流してみます。
サンプルテーブル2を使って説明します。

FETCHとORDER BY
SELECT id,name,bd FROM employee ORDER BY name ASC FETCH FIRST 2 ROWS ONLY

これはname列を昇順にして、その中から上位2行のレコードを取得するSQLです。ONLYを指定しているので、2行目と3行目のデータが同じでもすっぱり2行しか返しません。

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
11 SATOU 12-Apr-98

このようにnameの昇順で2行戻されます。nameがSATOUのデータが2つありますが、どちらの行が2行目になるかはこのSQLでは分かりません。ORACLE次第です。今回はidが11のSATOUが戻っていますが、13のSATOUが戻るかもしれません。このように、同じ値だから、どちらが戻るか分からない不安定なデータ戻しをしたくない場合はWITH TIESを使った方が良いでしょう。

次のSQLは、PERCENTとWITH TIESを使います。

FETCHとORDER BY
SELECT id,name,bd FROM employee ORDER BY name ASC FETCH FIRST 40 PERSENT ROWS WITH TIES;

全体の中で40%のレコードを返しますが、40%行目と次の行以降のnameが同じなら次の行以降も一生に返します。サンプルテーブル2は全体が5行のレコードなので40%は2行です。そしてWITH TIESなので2行目と3行目以降のnameデータが同じなら3行目以降も返します。
そのため、結果はこのようになります。

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
ISHII 9-Jan-00
13 SATOU 20-Mar-11
11 SATOU 12-Apr-98

2行目と3行目のnameは同じSATOUなので、3行目まで返します。

FETCHとOFFSETとORDER BY

では最後にOFFSETを使ったFETCHを紹介します。OFFSETは指定した行数分をスキップして、それ以降のデータを取得します。例えば、「name列を昇順で取得して3行目から2行分のみを戻したい」などです。
実際にサンプルテーブル1(※サンプルテーブル2ではありません!)使って、name列を昇順で取得して3行目から2行分のみを戻してみましょう。

FETCHとOFFSETとORDER BY
SELECT id,name,bd FROM employee ORDER BY name ASC OFFSET 2 FETCH FIRST 2 ROWS ONLY;

(テーブル名:employee、id:数値型、name:文字型、bd:日付(デフォルトはDD-MON-RR))

ID NAME BD
12 YAMADA
13 YASUDA 20-Mar-11

OFFSET 2なのでnameを昇順で並べた時の上位2行を排除し、FETCH FIRST 2 ROWS ONLY なので、3、4行目のデータを取得します。(5行目は排除します。)employeeテーブルで見てみると、1番目のISHIと2番目のSATOUは排除されて3番目のYAMADAと4番目のYASUDAが戻されます。5行目のNULLは排除されます。このように制限つきでレコードを取得したい時はOFFSETとFETCHをうまく使ってください。

ORDER BYについて長々と書きましたが、開発現場ではORDER BYはかなり良く使います。何万件もあるテーブルを調べたい時にはORDER BYを指定しないとデータ表示に一貫性がないので、見づらくて仕事になりません。ORDER BYはSQLの基礎です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です