ORDER BY を指定しないと、行の順序は不定
ORDER BY を付けない SELECT は、SQLite が都合よく見つけた順番で行を返します。小さなテーブルだと挿入順に_見えてしまう_ことが多く、それを信じ込んでしまう人が後を絶ちません。でも、これは当てにしないでください。インデックスが使われた瞬間、テーブルが大きくなったとき、あるいはクエリプランが変わったときに、順序は何の前触れもなく変わります。
行の並び順が大事なら、はっきり書きましょう。
ORDER BY name はデフォルトで昇順ソートになります。実行すると Ada、Boris、Chen、Rosa の順、つまりアルファベット順で返ってきます。テーブルがディスク上にどう格納されているかは関係なく、結果は常にこの並びです。
昇順（ASC）と降順（DESC）
ASC は昇順を意味します（小さい順、A から Z、古い順）。一方の DESC は降順、つまりその逆ですね。ASC はデフォルトなので、実際のクエリではほぼ省略されます：
そうすると、登録が新しい順に並びます。ISO 8601形式（YYYY-MM-DD）の文字列で保存された日付は、テキストとしてもそのまま正しい順序でソートされます。SQLiteには専用の日付型がないため、日付カラムにこのフォーマットが推奨される理由のひとつです。
sqliteで複数列をソートする（ORDER BY 複数カラム）
最初のソート列に同じ値が並んだとき、SQLiteは2番目の列で順序を決めます。優先順位の高い順にカンマ区切りでカラムを並べましょう。
行はまず国ごとにまとめられ（FR が US より先）、同じ国の中では名前順に並びます。各列にはそれぞれ別の並び順を指定できます。
国名は昇順、各国内では新しい順です。ASC と DESC は隣のカラムだけに効くので、後ろの列にまで引きずられることはありません。
式やエイリアスで並び替える
ORDER BY にはカラム名だけでなく、任意の式を指定できます。計算値で並べたいときに便利です。
SELECT句で付けたrevenueというエイリアスは、そのままORDER BYでも使えます。もちろんORDER BY price * quantity DESCのように式をもう一度書いても結果は同じです。
カラムの位置番号で並べ替えることもできますが、これはおすすめしません。
SELECT name, price FROM products ORDER BY 2 DESC;
2は SELECT リストの2番目のカラムを指しています。動きはしますが、後から誰かがカラムの並びを入れ替えると、ソートの意味がこっそり変わってしまいます。番号ではなく、カラム名かエイリアスでソートしましょう。
NULL はどこに並ぶのか
NULL は「値が不明」を意味するので、SQLite はソート時にこの不明な値をどこに置くか決める必要があります。デフォルトのルールはこうです: ASC なら NULL は先頭、DESC なら末尾。
Ada と Chen が日付を持つレコードよりも上に来てしまっていますね。「新しい順に並べたい」というケースでは、まず期待しない結果でしょう。これを直すには NULLS LAST を指定します。
これで実在する日付が先に並び、NULL は末尾に追いやられます。逆に NULLS FIRST を指定すれば NULL が先頭にきます。どちらも標準 SQL の構文で、SQLite 3.30 以降で使えます。
COLLATE NOCASE で大文字小文字を区別しないソート
SQLite のテキスト比較は、デフォルトでは「バイナリ比較」になっています。要するに Unicode のコードポイント順で並ぶので、大文字は小文字より 前 に来ます。そのため、'Zoe' が 'apple' より先に並んでしまうわけです。
結果は Boris, Zoe, ada, apple となり、大文字が先に並び、その後に小文字が続きます。大文字小文字を区別せずに並べ替えたい場合は、NOCASE コレーションを指定します。
これで ada, apple, Boris, Zoe という結果が得られます。NOCASE はあくまで ASCII の A〜Z と a〜z を同一視するだけで、アクセント記号や非 ASCII 文字までは正規化してくれません。本格的な国際化対応のソートが必要なら、アプリケーション側で独自の照合順序を用意することになりますが、英語圏のよくあるケースなら NOCASE で十分です。
ランダム順で並べ替える
ときには行をランダムな順番で取り出したいこともあります。たとえば「今日のおすすめ」を選ぶときや、テスト用にサンプル行を抽出したいときなどです。SQLite の random() 関数はランダムな整数を返すので、これを使って ORDER BY すれば OK です。
各行に新しい乱数が割り当てられ、その値で並べ替えが行われる仕組みです。小さなテーブルなら問題ありません。ただし大規模なテーブルでは、ORDER BY random() は遅くなります。すべての行に対して乱数を計算したうえで結果全体をソートする必要があるためです。巨大なテーブルから1行だけサンプリングしたい場合は、もっと工夫されたアプローチ(ランダムな rowid を選ぶ、など)のほうが速いです。
よくある落とし穴
ハマりがちなポイントをいくつか挙げておきます。
ORDER BYを書かずに順序を期待してしまう。 指定しなければ順序は未定義です。一見安定しているように見えても、実は保証されていません。- 文字列として格納された数値をソートしてしまう。 辞書順だと
'10'は'2'より前に来ます。数値として並べたい列は数値アフィニティで保存しておくか、ORDER BY CAST(value AS INTEGER)のようにキャストしましょう。 - 複数列で ASC と DESC を混ぜたときの勘違い。 並び順は列ごとに独立しています。
ORDER BY a, b DESCはaを昇順、bを降順でソートする指定であって、両方を降順にするわけではありません。 - 上位数件だけ欲しいのに巨大な結果セット全体をソートしてしまう。
ORDER BYはLIMITとセットで使い、ソート対象の列にインデックスを張りましょう。これは次の章のテーマです。
次のテーマ:LIMIT と OFFSET
ORDER BY は行を どう並べるか を SQLite に伝えるものでした。一方、LIMIT と OFFSET は 何件返すか と どこから始めるか を指定します。この2つはページネーションや「上位 N 件」クエリの基本となる組み合わせです。次回詳しく見ていきましょう。
よくある質問
SQLite で結果を並び替えるには？
SELECT の末尾に ORDER BY 句を付け、ソートしたい列名を指定します。例えば SELECT * FROM users ORDER BY name; で昇順ソート、降順にしたい場合は ORDER BY name DESC のように DESC を付けます。なお ORDER BY を付けない場合、行の順序は保証されません。たまたま揃って見えても、その順序に依存するコードを書くのは避けましょう。
複数の列でソートしたいときは？
カンマで区切って列名を並べます。例：ORDER BY country, name。SQLite はまず country で並べ、同じ値だった行を name でさらに並び替えます。列ごとに方向を変えることもでき、ORDER BY country ASC, signup_date DESC のような書き方も可能です。
大文字小文字を区別せずにソートするには？
ORDER BY で COLLATE NOCASE を指定します。例：ORDER BY name COLLATE NOCASE。SQLite はデフォルトでバイナリ照合順序を使うため、そのままだと Zoe が apple より前に来てしまいます。NOCASE を付ければ大文字と小文字を同じものとして扱ってくれます。
ソート結果で NULL はどこに来る？
デフォルトでは、昇順なら NULL が先頭、降順なら末尾に並びます。挙動を変えたい場合は NULLS FIRST / NULLS LAST を使います。例えば ORDER BY signup_date DESC NULLS LAST とすれば、日付がある行を上に、NULL の行を下に押し込められます。
