SELECT DISTINCT と ORDER BY を一緒に使う際の注意点

SELECT DISTINCT と ORDER BY を一緒に使う場合、ORDER BY で指定する項目は、SELECT リストにも含まれている必要があります。これは、SELECT DISTINCT で重複行を除去した後に、ORDER BY で結果を並べ替える必要があるためです。

なぜこの制限が必要なのか

SELECT DISTINCT は、テーブルから重複行を除去して結果を返すクエリです。一方、ORDER BY は、結果を特定の列に基づいて並べ替えるクエリです。

ORDER BY で指定する項目が SELECT リストに含まれていない場合、SELECT DISTINCT で重複行を除去した後、ORDER BY でどのように並べ替えるべきかが分からなくなります。

例

以下の例では、t テーブルから name と age 列の値を取得し、重複行を除去して結果を name 列に基づいて昇順に並べ替えます。

SELECT DISTINCT name, age
FROM t
ORDER BY name ASC;

一方、以下の例では、age 列は SELECT リストに含まれていないため、エラーが発生します。

SELECT DISTINCT name
FROM t
ORDER BY age ASC;

この制限を回避するには、ORDER BY で指定する項目を SELECT リストに追加する必要があります。

SELECT DISTINCT name, age
FROM t
ORDER BY age ASC;

代替方法

ORDER BY で指定する項目が、集計関数などの式の場合、SELECT リストに式を追加する代わりに、サブクエリを使用することができます。

以下の例では、name 列に基づいて結果を昇順に並べ替え、各名前の出現回数を表示するためにサブクエリを使用しています。

SELECT name, COUNT(*) AS count
FROM t
GROUP BY name
ORDER BY name ASC;

SELECT DISTINCT と ORDER BY を一緒に使う場合、ORDER BY で指定する項目は、SELECT リストにも含まれている必要があります。この制限を回避するには、ORDER BY で指定する項目を SELECT リストに追加するか、サブクエリを使用することができます。

-- テーブル t の作成
CREATE TABLE t (
  name VARCHAR(50),
  age INT
);

-- データの挿入
INSERT INTO t (name, age) VALUES ('John Doe', 30);
INSERT INTO t (name, age) VALUES ('Jane Doe', 31);
INSERT INTO t (name, age) VALUES ('John Doe', 30);

-- SELECT DISTINCT と ORDER BY を一緒に使用
SELECT DISTINCT name, age
FROM t
ORDER BY name ASC;

このコードを実行すると、以下の結果が表示されます。

name | age
------- | --------
John Doe | 30
Jane Doe | 31

-- SELECT DISTINCT と ORDER BY を一緒に使用 (エラー発生)
SELECT DISTINCT name
FROM t
ORDER BY age ASC;

Msg 1054, Level 15, State 1, Line 4
Column 'age' is invalid in the ORDER BY clause because it is not contained in either the SELECT list or the GROUP BY clause.

-- サブクエリを使用
SELECT name, COUNT(*) AS count
FROM t
GROUP BY name
ORDER BY name ASC;

name | count
------- | --------
John Doe | 2
Jane Doe | 1

SELECT DISTINCT と ORDER BY を一緒に使う場合のその他の方法

ORDER BY をサブクエリで使用する

SELECT *
FROM (
  SELECT DISTINCT name, age
  FROM t
) AS t1
ORDER BY age ASC;

この方法では、まず SELECT DISTINCT を使って重複行を除去した結果をサブクエリで取得します。その後、サブクエリから取得した結果を ORDER BY で並べ替えます。

GROUP BY を使用する

SELECT name, age
FROM t
GROUP BY name
ORDER BY age ASC;

この方法では、GROUP BY を使って名前ごとにグループ化し、各グループの最初の行のみを取得します。その後、取得した結果を ORDER BY で並べ替えます。

ウィンドウ関数を使う

SELECT name, age,
  ROW_NUMBER() OVER (PARTITION BY name ORDER BY age ASC) AS rn
FROM t;

この方法では、ウィンドウ関数 ROW_NUMBER() を使って、名前ごとに昇順に並べたときの各行の順位を取得します。その後、取得した順位に基づいて結果を並べ替えます。

SELECT DISTINCT と ORDER BY を一緒に使う場合、状況に応じて上記の方法を使い分けることができます。