環境
| SQLServer | SQLCMD |
|---|---|
| 2017 | 14.0.1000.169 |
| 各種設定はデフォルト。 |
前文
ORDER BY句は通常固定で列名を指定するが、動的な動作となるような記述ができる。
実装例は以下。
DECLARE @orderFlag INT = 1; -- 1:num1でソート, 2:num2でソート, 1、2以外:num1でソート
SELECT num1, num2
FROM (
VALUES
(1, 3)
, (2, 2)
, (3, 1)
)t(num1, num2)
ORDER BY
CHOOSE(@orderFlag, num1, num2, num1)
;
GO
DECLARE @orderFlag INT = 1;での実行結果は以下。
num1 num2
----------- -----------
1 3
2 2
3 1
(3 行処理されました)
DECLARE @orderFlag INT = 2;での実行結果は以下。
num1 num2
----------- -----------
3 1
2 2
1 3
(3 行処理されました)
よく、ORDER BY句ではCASE式を使うことができる、という説明を見かけるが厳密には、「式を指定することができる」となる。
そしてSQL ServerにはCHOOSE関数という便利なものがあるので、CASE式よりシンプルに記述ができる。
更に上記のような二択の場合なら、ORDER BY句はIIF(@orderFlag = 1, num1, num2)でもよい。
しかし、この式にASC DECを含めると文法エラーになる。よくある勘違い。
文法エラーの理由は公式ドキュメントを参照すれば分かる。
-- Syntax for SQL Server and Azure SQL Database ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] [ <offset_fetch> ] <offset_fetch> ::= { OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [ FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY ] }引数
order_by_expression
クエリの結果セットの並べ替えに使用する列または式を指定します。 並べ替え列は、列の名前、列の別名、または選択リスト内の列の位置を示す負以外の整数で指定できます。ASC | DESC
指定した列の値を昇順と降順のどちらで並べ替えるかを指定します。
つまり、ASC DESCは並べ替えに指定された列または式の降順／昇順を修飾する述語である。
式に含めることはできない。
と、ここまではよく指摘される話。
そこを何とかする方法、となると、ちょっとぐぐったところ出てこなかった。
動的に昇順／降順を切り替える方法を考察する。
ソート対象列が数値型の場合
連番のような数値型列であれば話は簡単。実装例は以下。
DECLARE @orderFlag INT = 1; -- 1:昇順, 1以外:降順
;WITH CTE_Src(seq) AS (
SELECT 1
UNION ALL
SELECT seq + 1 FROM CTE_Src WHERE seq < 10
)
SELECT seq FROM CTE_Src
ORDER BY
IIF(@orderFlag = 1, seq, -seq)
;
GO
DECLARE @orderFlag INT = 1;での実行結果は以下。
seq
-----------
1
2
3
4
5
6
7
8
9
10
(10 行処理されました)
DECLARE @orderFlag INT = 2;での実行結果は以下。
seq
-----------
10
9
8
7
6
5
4
3
2
1
(10 行処理されました)
ソート対象列の符号を反転すれば良いだけ。簡単かんたん。
しかし、文字列型等には対応できない。次は汎用的な方法を考察する。
汎用的な方法
実装例は以下。
DECLARE @orderFlag INT = 1; -- 1:昇順, 1以外:降順
;WITH CTE_Src(txt, orderAsc, orderDesc) AS (
SELECT
txt
, ROW_NUMBER() OVER(ORDER BY txt) -- 昇順
, ROW_NUMBER() OVER(ORDER BY txt DESC) -- 降順
FROM (
VALUES
('AAAA')
, ('BBBB')
, ('CCCC')
, ('DDDD')
)T(txt)
)
SELECT txt FROM CTE_Src
ORDER BY
IIF(@orderFlag = 1, orderAsc, orderDesc)
;
GO
DECLARE @orderFlag INT = 1;での実行結果は以下。
TXT
----
AAAA
BBBB
CCCC
DDDD
(4 行処理されました)
DECLARE @orderFlag INT = 2;での実行結果は以下。
TXT
----
DDDD
CCCC
BBBB
AAAA
(4 行処理されました)
無理矢理感満載だが、ウィンドウ関数を利用した方法となる。
しかしここまで汎用的になると、昇順／降順に限らず、複数列のソートも容易に記述することが可能になる。
実装例は以下。
DECLARE @orderFlag INT = 3; -- 1:ソート順1, 2:ソート順2, 3:ソート順3, 1～3以外:ソート順1
;WITH CTE_Src(txt1, txt2, order1, order2, order3) AS (
SELECT
txt1
, txt2
, ROW_NUMBER() OVER(ORDER BY txt1, txt2 DESC) -- ソート順1
, ROW_NUMBER() OVER(ORDER BY txt1 DESC, txt2) -- ソート順2
, ROW_NUMBER() OVER(ORDER BY txt1 DESC, txt2 DESC) -- ソート順3
FROM (
VALUES
('AAAA', 'aaaa')
, ('AAAA', 'bbbb')
, ('AAAA', 'cccc')
, ('BBBB', 'aaaa')
, ('BBBB', 'bbbb')
, ('BBBB', 'cccc')
)t(txt1, txt2)
)
SELECT txt1, txt2 FROM CTE_Src
ORDER BY
CHOOSE(@orderFlag, order1, order2, order3, order1)
;
GO
各ウィンドウ関数ROW_NUMBER()のORDER BY句に各ソート順パターンを記述すれば良いので可読性は高い。クエリ末尾のORDER BY句では、そのどれを使用するかを指定する形。
クエリ末尾のORDER BY句だけで制御する、複数列を指定するとなると、各式をカンマ区切りで記述することになり冗長なんだよね。
余談1
そもそもORDER BY句での列指定は、列の順番にも対応している。
ならば変数をそのまま割り当てればよくね？ と思い実行すると……。
1> DECLARE @orderFlag INT = 2;
2>
3> SELECT num1, num2
4> FROM (
5> VALUES
6> (1, 3)
7> , (2, 2)
8> , (3, 1)
9> )t(num1, num2)
10> ORDER BY
11> @orderFlag
12> ;
13> GO
メッセージ 1008、レベル 16、状態 1、サーバー *******-*******、行 11
ORDER BY 番号 1 で識別される SELECT 項目に、列位置を識別する式の一部として変数が含まれています。変数は、列名を参照する式で順序付けられているときだけ許可されます。
対応していないんだよなぁ。それで思い出すのがTOP句。
TOP (Transact-SQL)
B. 変数を指定して TOP を使用する
次の例では、クエリの結果セットで返される従業員の数を、変数を使用して指定します。
しかし以前は変数に対応していなかった。
ORDER BY句もいずれは……とも思ったが、そもそも列の順番での指定自体推奨されていないから、変数が対応される仕様変更は将来的にもないかな。
ベスト プラクティス
選択リスト内の列の位置を表すために、ORDER BY 句で整数を指定しないでください。 たとえば、SELECT ProductID, Name FROM Production.Production ORDER BY 2 などのステートメントは有効ですが、実際の列名を指定した場合と比べて理解が難しくなります。 さらに、列の順序を変更したり、新しい列を追加するなどして、選択リストに変更を加えた場合は、予期しない結果が生じないように ORDER BY 句を変更する必要が生じます。
余談2
冒頭で述べたように、ORDER BY句の指定はCASE式に限定されていないので、様々な記述が可能。
1> SELECT 1 ORDER BY SYSDATETIME();
2> SELECT 1 ORDER BY (SELECT 1);
3> SELECT 1 ORDER BY (SELECT 1 + 2) + 3;
4> SELECT 1 ORDER BY (SELECT COALESCE(1, 2));
5> SELECT 1 ORDER BY (SELECT NULLIF(1, 2));
6> SELECT 1 ORDER BY (SELECT LOG(10));
7> GO
-----------
1
(1 行処理されました)
-----------
1
(1 行処理されました)
-----------
1
(1 行処理されました)
-----------
1
(1 行処理されました)
-----------
1
(1 行処理されました)
-----------
1
(1 行処理されました)
PostgreSQLは真偽値を直接扱うことができる仕様からか、結果として通常であればWHERE句に指定するような式がORDER BY句でも指定することが可能で更にカオス。
mydb=# SELECT 1 ORDER BY EXISTS(SELECT 1);
?column?
----------
1
(1 行)
mydb=# SELECT 1 ORDER BY 1 IN (1, 2);
?column?
----------
1
(1 行)
mydb=# SELECT 1 ORDER BY (1 = 1);
?column?
----------
1
(1 行)
mydb=# SELECT 1 ORDER BY 1 BETWEEN 1 AND 2;
?column?
----------
1
(1 行)
うはは。何でこんなクエリが通るんだよ。最早大喜利状態。
勿論、これらは文法エラーが発生しないことを示しているだけで、クエリとしての意味はない。
逆にこうした無意味な指定は内部的にどのように処理されているのか気になり、実テーブルを使用し、実行プランを確認した。
しかし実行プランには現れなかった。どうやらORDER BY句の指定で実質的にソート順に作用しないと判断されると切り捨てられるらしい。
そのなこと当たり前？ しかしその性質を利用したハック的なテクニックも存在する。
しかしここに記すには余白が狭すぎるまた話が長くなるので、それは別の機会に。クエリの世界は果てしなく奥が深い。