ORDER BY句

ORDER BY句は、入力として受け取る行の順序を並べ替えます。2つの入力行間の相対順序は、各行についてORDER BY句にリストされている式を評価し、各ORDER BY式に関連付けられたsort_specを考慮して結果の値を比較することで決定されます。

構文

orderby_clause ::= ORDER BY 
   expression sort_spec 
   ("," expression sort_spec)*

sort_spec ::= [ASC|DESC] [NULLS (FIRST|LAST)]

セマンティクス

各順序付け式は、最大で1つのアトミック値を返す必要があります。順序付け式で空の順序が返された場合、戻り値として特別な値EMPTYが使用されます。SELECT式にGROUP BYも含まれる場合、ORDER BY内の式は、グループ化式(GROUP BY句がある場合は、その句内)、集計関数、またはグループ化式や集計関数に加えて作成された式である必要があります。

sort_spec: sort_specはソートの方向(昇順または降順)と、特別な値NULL、JNULL、およびEMPTYを特別ではない値と比較する方法を指定します。

NULLS LASTが指定されている場合、特別な値はすべての特別ではない値の後に表示されます。
NULLS FIRSTが指定されている場合、特別な値はすべての特別ではない値の前に表示されます。

3つの特別な値そのものの相対順序は固定されています。

方向がASCの場合、順序はEMPTY < JNULL < NULLです
それ以外の場合、順序付けは逆になります。

この構文では、sort_specはオプションです。

sort_specが指定されない場合、デフォルトはASC順序およびNULLS LASTです。
ソート順序のみが指定されている場合、順序がASCの場合はNULLS LASTが使用され、それ以外の場合はNULLS FIRSTが使用されます。
ソート順序が指定されていない場合、ASCが使用されます。

前述のルールを考慮し、2つの入力行の間の相対順序は、次のように決定されます。Nをorder-by式の数にし、Vi1、Vi2、… ViNを、行Riでこれらの式を左から右に評価して返されるアトミック値(EMPTYを含む)にします。2つ行のRi、Rjは、Vikが1、2、…、Nの各kのVjkと等しい場合に等しいとみなされます。このコンテキストでは、NULLは自身とのみ同等であるとみなされます。それ以外の場合、次のようなVimとVjmのペアがある場合、RiはRjよりも小さいとみなされます。

mが1であるか、Vikが1、2、…、(m-1)の各kのVjkと等しい、および
VimがVjmと等しくない、および
m番目のsort_specが昇順を指定し、VimがVjmより小さい、または
m番目のsort_specが降順を指定し、VimがVjmより大きい

前述のルールでは、2つの値(VikとVjk)の比較は、いずれも特別ではなく互いに比較できる場合、値比較演算子の項で定義された値比較演算子のルールに従って実行されます。

VikとVjkに比較可能なタイプがない場合(jsonフィールドでソートするときなどに発生する可能性があります)、次のルールが適用されます。

方向がASCの場合、順序付けは数値項目<タイムスタンプ<文字列および列挙<ブール値となります。
それ以外の場合、順序付けは逆になります。

グループ化と同様に、ソートは索引ベースまたは汎用のいずれかです。索引ベースのソートが可能なのは、目的の順序で行をソートする索引がある場合のみです。より正確には、e1、e2、…、eNをORDER BY句に表示されるとおり(左から右)にORDER BY式とします。次に、1、2、...、Nの各iについてeiがi番目の索引フィールドの定義に一致する索引(主キー索引または既存の2次索引の1つ)が存在する必要があります。さらに、すべてのsort_specに同じ順序付けの方向を指定し、各sort_specについて、特別な値に関して必要な順序が、それらの値を索引でソートする方法と一致する必要があります。現在の実装では、特別な値は常に索引の最後にソートされます。そのため、ソート順序がASCの場合は、すべてのsort_specでNULL LASTを指定する必要があります。また、ソート順序がDESCの場合は、すべてのsort_specでNULLS FIRSTを指定する必要があります。

ノート:

適切な索引が存在しない場合または問合せオプティマイザによって選択されていない場合、ソートは汎用になります。これは、ソート前に、すべての問合せ結果をドライバ・メモリーにフェッチしてそこにキャッシュする必要があることを意味します。したがって、グループ化と同様に、汎用ソートではドライバ・メモリーを大量に消費する可能性があるため、避けるのが最善です。

汎用ORDER BYとGROUP BYのどちらについても、アプリケーションでは、このような操作がクライアント・ドライバで消費できるメモリー量をプログラムで指定できます。次に示すように、使用可能な各言語ドライバには、この機能に固有のメソッドがあります。

表6-1 メモリー消費に関するAPI

言語ドライバ	最大メモリー消費の取得	最大メモリー消費の設定
Java	getMaxMemoryConsumption()	setMaxMemoryConsumption(long v)
Python	get_max_memory_consumption()	set_max_memory_consumption (memory_consumption)
Node.js	maxMemoryMB	maxMemoryMB
Go	GetMaxMemoryConsumption()	MaxMemoryConsumption

例6-23 ORDER BY句

この例では、年齢が30歳を超えるユーザーのIDと姓を選択し、IDでソートした結果を返します。IDがユーザー表の主キーであるため、この場合はソートが可能です。

SELECT id, lastName 
FROM users 
WHERE age > 30 
ORDER BY id;

例6-24 ORDER BY句

この例では、年齢が30歳を超えるユーザーのIDと姓を選択し、年齢でソートした結果を返します。ソートは、age列に2次索引(または、通常は、最初の列がage列である複数列索引)がある場合にのみ可能です。

SELECT id, lastName 
FROM users 
WHERE age > 30 
ORDER BY age;

例6-25 ORDER BY句

次の例では、名でソートされたすべての行を返します。

SELECT id, firstName, lastName
    FROM users
    ORDER BY firstName;
 +----+-----------+----------+
 | id | firstName | lastName |
 +----+-----------+----------+
 | 10 | John      | Smith    |
 | 20 | Mary      | Ann      |
 | 30 | Peter     | Paul     |
 +----+-----------+----------+
3 rows returned

例6-26 ORDER BY句

次の例では、firstName、lastNameおよびincomeをincomeで最高から最低にソートして返します。

SELECT firstName, lastName, income
    from users
    ORDER BY income DESC;
 +-----------+----------+--------+
 | firstName | lastName | income |
 +-----------+----------+--------+
 | Mary      | Ann      |  90000 |
 | Peter     | Paul     |  53000 |
 | John      | Smith    |  45000 |
 +-----------+----------+--------+
3 rows returned

例6-27 ORDER BY句

次の例では、データを年齢別にグループ化し、その年齢に該当するユーザーの数と平均収入を平均収入順に返します。

SELECT
    age, count(*), avg(income)
    FROM users
    GROUP BY age
    ORDER BY avg(income);
 +-----+----------+----------+
 | age | Column_2 | Column_3 |
 +-----+----------+----------+
 |  22 |        1 |  45000.0 |
 |  25 |        1 |  53000.0 |
 |  43 |        1 |  90000.0 |
 +-----+----------+----------+
3 rows returned

例6-28 ORDER BY句

次の例では、問合せ1は州および収入を収入でソートして返します。ただし、問合せ1を州別にグループ化する場合は、GROUP BY句を使用できます。しかし、SELECT式にグループ化が含まれる場合、SELECT句およびORDER BY句内の式は、グループ化式、集計関数または外部変数のみを参照する必要があります。そのため、目的の結果を得るには、問合せ1を問合せ2に示すように作成しなおす必要があります。

Query 1:
 
SELECT
    u.address.state, u.income
    FROM users u
    ORDER BY u.income;
 +---------------+--------+
 |     state     | income |
 +---------------+--------+
 | NV            |  45000 |
 +---------------+--------+
 | CA            |  53000 |
 +---------------+--------+
 | CA            |  90000 |
 +---------------+--------+
3 rows returned
 
Query 2:
 
SELECT
    u.address.state, max(u.income)
    FROM users u
    GROUP BY u.address.state
    ORDER BY max(u.income);
 +---------------+----------+
 |     state     | Column_2 |
 +---------------+----------+
 | NV            |    45000 |
 +---------------+----------+
 | CA            |    90000 |
 +---------------+----------+
2 rows returned

例6-29 ORDER BY句

次の例では、問合せ1はusers表のすべての行の収入および州を返します。問合せ2は、各州の平均収入を取得します。

Query 1:
 
SELECT
    u.address.state, u.income
    FROM users u;
 +---------------+--------+
 |     state     | income |
 +---------------+--------+
 | CA            |  53000 |
 +---------------+--------+
 | NV            |  45000 |
 +---------------+--------+
 | CA            |  90000 |
 +---------------+--------+
3 rows returned
 
Query 2:
 
SELECT
    u.address.state, avg(u.income)
    FROM users u
    GROUP BY u.address.state
    ORDER BY avg(u.income);
 +---------------+----------+
 |     state     | Column_2 |
 +---------------+----------+
 | NV            |  45000.0 |
 +---------------+----------+
 | CA            |  71500.0 |
 +---------------+----------+
2 rows returned