ORDER BY句
簡単なSELECT文へ戻る。
[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人'; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0008 | ステカセキング | 214 | 700 | 250 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0014 | バッファローマン | 250 | 220 | 1000 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0020 | サンシャイン | 300 | 1000 | 700 | +------+-----------------------------+--------+--------+-------+ [/sql]
今回話題にしたいのは、結果の内容ではなく、結果が表示されるときの並び順である。
さて、この13行の結果は、いったいどのような順番で並んでいるのだろうか。一見すると、idの小さい順（昇順）のように見える。しかし実はこれはただの偶然にすぎない。答えはランダムなのである。したがって、次に同じSELECT文を実行したときには、前回とは違う結果で表示されるかもしれない。
一般に、テーブルからデータを選択する場合、その順番は、特に指定がない限り、どんな順番で並ぶかは全く分からない。同じSELECT文ですら、実行するたびに並び順が変わる可能性がある。
しかし、順番がちゃんと並んでいないと、結果を使いづらい場合も多くある。そういうケースにおいては、SELECT文の文末にORDER BY句をつけることで明示的に行の順序を指定する。
例えば、悪魔超人を、超人強度（power）の低い順に並べる場合は以下のようになる。
[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0014 | バッファローマン | 250 | 220 | 1000 | +------+-----------------------------+--------+--------+-------+ [/sql]
このORDER BY句は、いつどんな場合でも、SELECT文の最後に記述する。これは、行の並べ替え（ソート）は、結果を返す直前で行う必要があるからである。また、ORDER BY句に書く列名を「ソートキー」と呼ぶ。ほかの句との順序関係を表すと次のようになる。
1.SELECT句 → 2.FROM句 → 3.WHERE句 → 4.GROUP BY句 → 5.HAVING句 → 6.ORDER BY句
なお、ORDER BY句は、行の指定をしたいと思わなければ書かなくとも良い。
昇順と降順の指定
上記の例とは反対に、超人強度（power）の高い順、つまり降順に並べる場合は、以下のように列の後ろにDESCキーワードを使う。
[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power DESC; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0014 | バッファローマン | 250 | 220 | 1000 | | 0019 | アシュラマン | 203 | 200 | 1000 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | +------+-----------------------------+--------+--------+-------+ [/sql]
実は昇順に並べる場合にも、正式にはASCというキーワードがあるのだが、省略した場合は暗黙に昇順に並べるという約束になっている。
このASCとDESCのキーワードは列単位で指定するものなので、1つの列は昇順を指定し、別の列は降順を指定する、ということも可能である。
複数のソートキーを指定する
ここで、上記で示した超人強度（power）の昇順に並べるSELECT文をもう一度見てみる。1000の超人が2人いることがわかる。この同じ超人強度の超人の順序は、特に指定がない限り、またしてもランダムである。
もし、この「同順位」の超人についても細かく並び順を指定したい場合は、もう1つソートキーを追加する必要がある。ここではidの昇順としてみる。すると以下のようになる。
[sql] SELECT id, name, height, weight, power FROM chojin WHERE category = '悪魔超人' ORDER BY power, id; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+-------+ | id | name | height | weight | power | +------+-----------------------------+--------+--------+-------+ | 0011 | ザ・魔雲天 | 285 | 1000 | 50 | | 0009 | ブラックホール | 201 | 420 | 200 | | 0010 | ミスター・カーメン | 200 | 150 | 200 | | 0008 | ステカセキング | 214 | 700 | 250 | | 0012 | アトランティス | 200 | 130 | 250 | | 0013 | スプリングマン | 104 | 600 | 320 | | 0018 | ザ・ニンジャ | 190 | 115 | 360 | | 0015 | スニゲーター | 210 | 180 | 400 | | 0016 | プラネットマン | 254 | 420 | 500 | | 0017 | ジャンクマン | 190 | 115 | 600 | | 0020 | サンシャイン | 300 | 1000 | 700 | | 0014 | バッファローマン | 250 | 220 | 1000 | | 0019 | アシュラマン | 203 | 200 | 1000 | +------+-----------------------------+--------+--------+-------+ [/sql]
このように、ORDER BY句には、複数のソートキーを指定することが可能。左側のキーから優先的に使用され、そのキーで同じ値が存在した場合に、右のキーが参照される、というルールである。もちろん、3列以上のソートキーを使うことも可能。
NULLの順番
これまでの例では、超人強度（power）をソートキーにしてきたが、今度は身長（height）をソートキーに使ってみる。このとき問題になるのは、スペシャルマンとカナディアンマンの行にあるNULLである。いったい、NULLというのはどういう順序づけがされるのだろうか。
以前のlessonで「NULLに比較演算子は使えない」で学んだ内容を思い出せば良い。NULLには比較演算子は使えないのである。すなわち、NULLと数値の順序付けはできない。文字や日付とも比較できない。したがって、NULLを含む列をソートキーにした場合、NULLは先頭または末尾にまとめて表示される。
[sql] SELECT id, name, height FROM chojin WHERE category = '正義超人' ORDER BY height; [/sql]
結果
[sql] +------+--------------------------+--------+ | id | name | height | +------+--------------------------+--------+ | 0043 | スペシャルマン | NULL | | 0044 | カナディアンマン | NULL | | 0001 | キン肉マン | 185 | | 0002 | テリーマン | 190 | | 0007 | ウルフマン | 190 | | 0006 | ブロッケンＪｒ | 195 | | 0005 | ラーメンマン | 209 | | 0004 | ウォーズマン | 210 | | 0003 | ロビンマスク | 217 | +------+--------------------------+--------+ [/sql]
先頭に来るか末尾に来るかは特に決まっていない。
ソートキーに表示用の別名を使う
GROUP BY句には、SELECT句でつけた別の列名は使うことが許されていない。一方、ORDER BY句ではそれが許されている。
[sql] SELECT name, power AS p FROM chojin WHERE category = '悪魔超人' ORDER BY p; [/sql]
結果
[sql] +-----------------------------+------+ | name | p | +-----------------------------+------+ | ザ・魔雲天 | 50 | | ブラックホール | 200 | | ミスター・カーメン | 200 | | ステカセキング | 250 | | アトランティス | 250 | | スプリングマン | 320 | | ザ・ニンジャ | 360 | | スニゲーター | 400 | | プラネットマン | 500 | | ジャンクマン | 600 | | サンシャイン | 700 | | アシュラマン | 1000 | | バッファローマン | 1000 | +-----------------------------+------+ [/sql]
なぜ、GROUP BY句では使えない別名が、ORDER BY句で使えるのか。その理由は、DBMS内部でSQL文が実行される順序に隠されている。SELECT文の実行順序は、句単位で見ると次のようになっている。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
ここで重要なのは「GROUP BYよりも後で、ORDER BYよりも前」というSELECT句の位置である。したがって、GROUP BY句が実行される時点では、SELECT句でつけることになっている別名を認識できない。SELECT句より後ろのORDER BY句ならば、その心配はないという訳である。
ORDER BY句に使える列
ORDER BY句には、テーブルに存在する列であれば、SELECT句に含まれていない列でも指定できる。
[sql] SELECT name, height, weight FROM chojin WHERE category = '正義超人' ORDER BY id; [/sql]
結果
[sql] +--------------------------+--------+--------+ | name | height | weight | +--------------------------+--------+--------+ | キン肉マン | 185 | 90 | | テリーマン | 190 | 95 | | ロビンマスク | 217 | 155 | | ウォーズマン | 210 | 150 | | ラーメンマン | 209 | 130 | | ブロッケンＪｒ | 195 | 90 | | ウルフマン | 190 | 102 | | スペシャルマン | NULL | 0 | | カナディアンマン | NULL | 0 | +--------------------------+--------+--------+ [/sql]
idはSELECT句に含まれていないが、idの昇順で並んでいる。
また、集約関数も使用できる。
[sql] SELECT category, COUNT(<em>) FROM chojin GROUP BY category ORDER BY COUNT(</em>) DESC; [/sql]
結果
[sql] +--------------+----------+ | category | COUNT(*) | +--------------+----------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+----------+ [/sql]
列番号は使ってはいけない
ORDER BY句では、SELECT句に含まれる列を参照する列番号を使うことができる。列番号とは、SELECT句で指定した列を左から1,2,3・・・と順番に割り振った番号である。したがって、以下の2つのSELECT分は同じ意味になる。
[sql] SELECT id, name, height, weight FROM chojin WHERE category = '悪魔超人' ORDER BY height DESC, id; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+ | id | name | height | weight | +------+-----------------------------+--------+--------+ | 0020 | サンシャイン | 300 | 1000 | | 0011 | ザ・魔雲天 | 285 | 1000 | | 0016 | プラネットマン | 254 | 420 | | 0014 | バッファローマン | 250 | 220 | | 0008 | ステカセキング | 214 | 700 | | 0015 | スニゲーター | 210 | 180 | | 0019 | アシュラマン | 203 | 200 | | 0009 | ブラックホール | 201 | 420 | | 0010 | ミスター・カーメン | 200 | 150 | | 0012 | アトランティス | 200 | 130 | | 0017 | ジャンクマン | 190 | 115 | | 0018 | ザ・ニンジャ | 190 | 115 | | 0013 | スプリングマン | 104 | 600 | +------+-----------------------------+--------+--------+ [/sql]
[sql] SELECT id, name, height, weight FROM chojin WHERE category = '悪魔超人' ORDER BY 3 DESC, 1; [/sql]
結果
[sql] +------+-----------------------------+--------+--------+ | id | name | height | weight | +------+-----------------------------+--------+--------+ | 0020 | サンシャイン | 300 | 1000 | | 0011 | ザ・魔雲天 | 285 | 1000 | | 0016 | プラネットマン | 254 | 420 | | 0014 | バッファローマン | 250 | 220 | | 0008 | ステカセキング | 214 | 700 | | 0015 | スニゲーター | 210 | 180 | | 0019 | アシュラマン | 203 | 200 | | 0009 | ブラックホール | 201 | 420 | | 0010 | ミスター・カーメン | 200 | 150 | | 0012 | アトランティス | 200 | 130 | | 0017 | ジャンクマン | 190 | 115 | | 0018 | ザ・ニンジャ | 190 | 115 | | 0013 | スプリングマン | 104 | 600 | +------+-----------------------------+--------+--------+ [/sql]
2つ目のSELECT文のORDER BY句は「SELECT句の3番目の列で降順ソートし、続いて1番目の列で昇順ソートする」という意味になる。
列番号で指定する書き方は、列名を書かなくて良いという手軽さがあって非常に便利であるが、次の2つの理由から使うべきではない。 理由1 コードが読みにくい。列番号を使用すると、ORDER BY句を見ただけではどんな列をソートキーにしているのかわからず、SELECT句のリストを先頭から数えなければならない。この例ではSELECT句の列数が少ないため、あまり気にならないかもしれないが、実務ではもっとたくさんの列を含めることもあるし、SELECT句とORDER BY句の間に大きなWHERE句やHAVING句がはさまって、目で追うことが大変な場合もある。 理由2 この順番項目の機能は、SQL-92において、「将来削除されるべき機能」に挙げられた。したがって、現在は問題なくても、将来、DBMSのバージョンアップを行った際に、これまで動いていたSQLが突如エラーになるという厄介な問題を引き起こす可能性がある。その場限りの使い捨てのSQLならまだしも、システムに組み込むSQLでこの機能を使うことは避けたほうがよい。
