ORDER BY (SQL)

結果セットでの行のソートを指定する SELECT 節です。

Synopsis

ORDER BY orderItem ORDER BY orderItem [ASC | DESC] ORDER BY orderItem [ASC | DESC], orderItem2 [ASC | DESC]

概要

ORDER BY は、指定された 1 つ以上の順序付け項目 (通常は列) で、クエリの結果セットの行をソートします。ORDER BY は、SELECT 文内の最後の節として、FROM、WHERE、GROUP By、および HAVING 節の後に指定されます。以下に例を示します。

SELECT Name, AVG(Age) AS AvgAge, Home_State
FROM Sample.Person
GROUP BY Home_State
ORDER BY AvgAge

ORDER BY orderItem は、列などの指定された順序付け項目の値で、クエリの結果セットの行をソートします。行は、昇順に返されます。
以下の文は、照会された行を Home_State 列を基準に昇順でソートして返します。
```
SELECT Name,Age,Home_State
FROM Sample.Person
ORDER BY Home_State
```
例：列名でのソート
ORDER BY orderItem [ASC | DESC] は、値を昇順 (ASC) あるいは降順 (DESC) のいずれかでソートします。
以下の文は、照会された行を Home_State 列を基準に降順でソートして返します。
```
SELECT Name,Age,Home_State
FROM Sample.Person
ORDER BY Home_State DESC
```
例： ORDER BY での TOP 節の使用
ORDER BY orderItem [ASC | DESC], orderItem2 [ASC | DESC] は、1 つ以上の順序付け項目で、値を順番にソートします。
以下の文では、照会された行を、最初に Home_State 列を基準に昇順でソートし、次に Age 列を基準に降順でソートして返します。
```
SELECT Name,Age,Home_State
FROM Sample.Person
ORDER BY Home_State,Age DESC
```
例：

この ORDER BY 節は、SELECT リスト内のウィンドウ関数が実行された後 (ウィンドウ関数自体の ORDER BY 節を含む) に適用されます。このため、ウィンドウ関数から返される値は、SELECT クエリの ORDER BY 節の影響を受けません。

ORDER BY 節を省略すると、返される行の順序は指定されず、文の実行ごとに異なる可能性があります。

ORDER BY は、現在の選択モード設定に関係なく、論理 (内部ストレージ) データ値で行をソートします。ORDER BY で行をソートする方法の詳細は、"照合での ORDER BY" を参照してください。

引数

orderItem

クエリ結果セットをソートする順序を指定する項目、または項目のコンマ区切りリスト。orderItem で、以下のいずれかを組み合わせとして項目を指定することができます。

テーブル内の列の名前。列を SELECT リストで指定する必要はありません。列名では、大文字と小文字は区別されません。テーブルにない列名を指定すると、SQLCODE -29 エラーが生成されます。
テーブル内の列のエイリアス。エイリアスは SELECT リストで指定する必要があります。列のエイリアスでは、大文字と小文字は区別されません。
符号なしの数値リテラルとして指定される、テーブル内の列の数。この数は、SELECT リストで定義された列の順序に基づきます。SELECT リストの列に対応しない列番号を指定すると、SQLCODE -5 エラーが発生します。
orderItem の最初の文字が数字の場合、InterSystems IRIS® は列番号を指定していると見なします。整数のトランケーション・ルールは、整数以外の値を整数に解決するために適用されます。例えば、1.99 は 1 に解決されます。最初の文字が数字以外の場合、orderItem は列名または列のエイリアスと見なされます。
列番号を変数または式の結果として指定することはできません。列番号を括弧で囲むことはできません。
テーブル内の列で評価される式 (ORDER BY LENGTH(Name) など)。
ウィンドウ関数 (ORDER BY ROW_NUMBER() OVER (PARTITION BY State) など)。
集約関数 (SELECT リストでも指定されている場合)。集約関数が ORDER BY 節のみで指定された場合、SQLCODE -73 エラーが生成されます。

いくつかの例外はありますが、orderItem はリテラルとして指定する必要があります。列の名前を文字列として指定する変数またはその他の式を使用することはできません。orderItem が有効な識別子 (列の名前または列のエイリアス)、または符号なし整数 (列番号) として解釈されない場合、その orderItem は無視され、ORDER BY の実行はコンマ区切りリストで次の orderItem に進みます。orderItem 値が無視される例としては、以下のようなものがあります。

ダイナミック SQL ? 入力パラメータ
埋め込み SQL :var ホスト変数
サブクエリ
数値に解決される式
符号付きの数字
括弧で囲まれた数字

orderItem プロパティが非常に長い文字列であるか、複数の長い orderItems で ORDER BY を使用しようとすると、InterSystems SQL はエラーを生成します。これを回避するには、関連するフィールドで TRANCATE 照合を定義します。一般には、128 文字で切り捨てると安全です。

例

列名でのソート

以下の文は、列名でソートします。

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY Home_State,Name

ソート列が SELECT リストにあるかどうかに関係なく、列の名前でソートできます。例えば、この文は、SELECT リストに Home_State 列が含まれていなくても、前の文と同じ順序で同じ行を返します。

SELECT Name,DOB
FROM Sample.Person
ORDER BY Home_State,Name

RowID がプライベートで、SELECT リストに示されていない場合でも、RowID 値でソートできます。%ID 疑似列名を、実際の RowID 名ではなく、orderItem として指定します。以下に例を示します。

SELECT Name,DOB
FROM Sample.Person
ORDER BY %ID

ORDER BY 節は、orderItem の一部として、テーブル名またはテーブル・エイリアスを指定できます。

SELECT P.Name AS People,E.Name As Employees
FROM Sample.Person AS P,Sample.Employee AS E 
ORDER BY P.Name

ORDER BY 節では、矢印構文 (–>) 演算子を使用して、ベース・テーブルではないテーブル内の列を指定できます。

SELECT Name,Company->Name AS CompName
FROM Sample.Employee ORDER BY Company->Name,Name

列のエイリアスでのソート

以下の文は、列のエイリアスでソートします。

SELECT Name,Home_State AS HS,DOB
FROM Sample.Person
ORDER BY HS,Name

この文は、エイリアスを持つ SELECT リスト内の式でソートします。

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1) AS AgeDev
FROM Sample.Employee ORDER BY AgeDev,Name

列番号でのソート

以下の文は、列番号、つまり SELECT リストで指定されている取得された列の数値順でソートします。Home_State (列 2) でソートしてから、Name (列 1) でソートします。

SELECT Name,Home_State,DOB
FROM Sample.Person
ORDER BY 2,1

この文は、列番号を使用して SELECT リスト内の式でソートします。

SELECT Name,Age,$PIECE(AVG(Age)-Age,'.',1)
FROM Sample.Employee ORDER BY 3,Name

SELECT * 結果を列番号でソートすると、RowID がパブリックの場合、列 1 としてカウントされます。

SELECT * FROM Sample.Person ORDER BY 1

ORDER BY での TOP 節の使用

SELECT 文が ORDER BY および TOP 節を指定すると、返される "上位" 行は、ORDER BY 節で指定された順序に基づきます。例えばこの文は、MyTable から、最も高い年齢の値を持つ 5 行を、年齢の高い方から順に返します。

SELECT TOP 5 Name,Age FROM MyTable ORDER BY Age DESC

RowID でソートすると、TOP 節で選択される行が変わります。例えば、連続した RowID を持つ 100 行があるテーブルについて考えます。以下の文はそれぞれ行 1、2、3、4、5 および行 100、99、98、97、96 を返します。

SELECT TOP 5 %ID FROM MyTable ORDER BY %ID

SELECT TOP 5 %ID FROM MyTable ORDER BY %ID DESC

リスト・データに基づくソート

以下の文は、InterSystems IRIS リスト・データを含む列でソートします。InterSystems IRIS リストは書式設定文字で始まるエンコードされた文字列であるため、この文では、$LISTTOSTRING を使用して、リスト要素のエンコーディングではなく、実際の列値でソートしています。

SELECT Name,FavoriteColors
FROM Sample.Person
WHERE FavoriteColors IS NOT NULL
ORDER BY $LISTTOSTRING(FavoriteColors)

ホスト変数値に基づく項目のソート

CASE 式を使用して汎用のクエリを定義し、提供されるホスト変数の値に基づいてそのクエリを並べ替えることができます。例えば、以下の例では、var の値に応じて Name または Age で並べ替えることができます。

SELECT Name,Age FROM Sample.Person ORDER BY 
CASE WHEN :var=1 then Name
     WHEN :var=2 then Age END

この文は、2 つの CASE 式を指定します。真に評価された方の CASE で並べ替えられます。両方の CASE が真に評価された場合は、Country 順に並べ替えられ、Country 内で City 順に並べられます。

SELECT Country,City FROM Sample.Person ORDER BY 
CASE WHEN :var1=1 then Country END,
     WHEN :var2=1 then City END

引数 ASC および DESC は、CASE END キーワードの後に指定します。

CASE 式内のフィールドは列名で指定する必要があります。列のエイリアスと列番号はこのコンテキストでは許可されていません。

ダイナミック SQL と埋め込み SQL を使用した項目のソート

ダイナミック SQL は入力パラメータを使用して、リテラル値を ORDER BY 節に指定できます。入力パラメータを使用して、列名、列のエイリアス、列番号、照合キーワードを指定することはできません。以下のダイナミック SQL の例では、入力パラメータを使用して、名 (first name) で結果セットの行をソートします。

  set myquery = 4
  set myquery(1) = "SELECT TOP ? Name,Age,"
  set myquery(2) = "CURRENT_DATE AS Today"
  set myquery(3) = "FROM Sample.Person WHERE Age > ?"
  set myquery(4) = "ORDER BY $PIECE(Name,',',?)"

  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(.myquery)
  if qStatus '= 1 {
    write "%Prepare failed:"
    do $System.Status.DisplayError(qStatus)
    quit }

  set rset = tStatement.%Execute(10,60,2)
  do rset.%Display()
  write !,"%Display SQLCODE=",rset.%SQLCODE

以下のカーソルベースの埋め込み SQL の例は、同じ操作を実行しています。

  SET topnum=10,agemin=60,firstname=2

  &sql(DECLARE pCursor CURSOR FOR
       SELECT TOP :topnum Name,Age,CURRENT_DATE AS Today
       INTO :name,:years,:today FROM Sample.Person
       WHERE Age > :agemin
       ORDER BY $PIECE(Name,',',:firstname) )

  &sql(OPEN pCursor)
       QUIT:(SQLCODE'=0)

  FOR { &sql(FETCH pCursor)
        QUIT:SQLCODE
        WRITE "Name=",name," Age=",years," today=",today,!
      }

  &sql(CLOSE pCursor)

制限事項

SELECT クエリが ORDER BY 節を指定する場合、結果のデータは更新できません。したがって、後に続く DECLARE CURSOR FOR UPDATE 文を指定する場合、FOR UPDATE 節は無視され、カーソルは読み取り専用で宣言されます。
ORDER BY が UNION に適用される場合、順序付け項目は数字か単純な列名にします。式は使用できません。列名を使用する場合、列の名前付けが UNION の最初の SELECT リストで行われたものとして結果の列を参照します。
ORDER BY 節をサブクエリで使用する場合は、TOP 節と組み合わせる必要があります。これは TOP ALL 節になる場合もあります。例えば、以下のクエリは、ソートされたサブクエリで DISTINCT 節を使用しているため、有効ではありません。
```
SELECT Name FROM Sample.Person WHERE Name = 
  (SELECT DISTINCT Name FROM Sample.Employee ORDER BY Title)
```
以下のクエリは、代わりにソートされたサブクエリで TOP ALL を使用しているため有効です。
```
SELECT Name FROM Sample.Person WHERE Name = 
  (SELECT TOP ALL Name FROM Sample.Employee ORDER BY Title)
```
400 文字を超える ORDER BY orderItem 値でクエリを実行すると、SQL -400 の深刻なエラーが発生する場合があります。この現象は、InterSystems IRIS システムの固定の制限である、エンコードされるグローバル参照の最大長の制限によるものです。この問題を防ぐためには、ORDER BY 節の基礎になるフィールドの照合設定でトランケーション・レングスを使用します。
例えば、このクエリの NarrativeSummary 列が 400 文字を超えているとします。
```
SELECT LocationCity,NarrativeSummary FROM Aviation.Event
WHERE LocationCity %STARTSWITH 'Be'
ORDER BY NarrativeSummary
```
maxlen 切り捨て長を含む照合関数を追加すると、このクエリを正常に実行できます。
```
SELECT LocationCity,NarrativeSummary FROM Aviation.Event
WHERE LocationCity %STARTSWITH 'Be'
ORDER BY %SQLUPPER(NarrativeSummary,400)
```

パフォーマンス

ORDER BY 節で使用されているリテラル値ごとに、異なるクエリ・キャッシュが生成されます。ORDER BY リテラルではリテラル置換は実行されません。これは、ORDER BY は整数を使用して列番号を指定できるためです。この整数が変わると、クエリはまったく異なる結果になります。

詳細

照合での ORDER BY

ソートは照合順で実行されます。既定では、文字列値の順序付けは、作成時に ORDER BY orderItem 列に対して指定された照合に基づいて行われます。InterSystems IRIS ネームスペースが SQLUPPER の既定の文字列照合を使用している場合、照合での ORDER BY では大文字と小文字を区別しません。

数値データ型フィールドの順序付けは、数値照合に基づいて行われます。式に対しては、既定照合は EXACT です。

照合関数を適用することで、列の既定照合をオーバーライドできます。例えば、ORDER BY %EXACT(Name) のように使用します。列のエイリアスに照合関数を適用することはできません。これを実行しようとすると、SQLCODE -29 エラーが生成されます。

既定の昇順照合シーケンスは、空文字列 ('') よりも NULL を最小値とします。ORDER BY は、空白スペースのみで構成される文字列と空文字列を区別しません。

列に指定された照合が英数字である場合、先頭の数字は、整数順ではなく、文字照合順でソートされます。整数順で並べるには、%PLUS 照合関数を使用できますが、この関数は数値以外の文字すべてを 0 として扱います。

混合数値文字列を数値順に正しくソートするには、複数の ORDER BY orderItem を指定する必要があります。次の形式の Home_Street 列について考えます。

Number StreetName StreetType

Number は、整数の番地です。StreetName と StreetType は、結合されて "Elm Street" などのような完全なストリート名を形成する文字列です。

以下の文では、住所を文字照合順にソートします。

SELECT Name,Home_Street FROM Sample.Person
ORDER BY Home_Street

以下の文では、番地を整数順でソートし、ストリート名を文字照合順でソートしています。この文には式が含まれ、列のエイリアスや列番号ではなく、列名のみを操作します。

SELECT Name,Home_Street FROM Sample.Person
ORDER BY $PIECE(%PLUS(Home_Street),' ',1),$PIECE(Home_Street,' ',2),$PIECE(Home_Street,' ',3)

ASC 照合と DESC 照合

列識別子の前の、オプションの ASC (昇順) や DESC (降順) キーワードで指定されたように、各列のソートは昇順または降順の照合順で指定できます。ASC や DESC が指定されていない場合、ORDER BY は列を昇順でソートします。ASC または DESC キーワードは、ダイナミック SQL の ? 入力パラメータや埋め込み SQL の :var ホスト変数を使用して指定することはできません。

NULL は常に、ASC 順では最も低い値であり、DESC 順では最も高い値となります。

複数のコンマ区切りの ORDER BY 値は、並べ替え操作の階層を指定します。例えば以下の文では、SELECT 節リスト内の 3 番目にリストされた項目 (C) のデータ値を昇順でソートします。この順序内で、7 番目にリストされた項目 (J) の値を降順でソートします。この中で、1 番目にリストされた項目 (A) の値を昇順でソートします。

SELECT A,B,C,M,E,X,J
FROM LetterTable
ORDER BY 3,7 DESC,1 ASC

ORDER BY 値のリスト内の重複した列は影響がありません。これは、2 番目のソートが最初のソート順序内であるためです。例えば、ORDER BY Name ASC, Name DESC では、Name 列を昇順でソートします。

NLS 照合

既定以外の NLS (各国言語サポート) 照合を指定した場合は、すべての照合が並べられており、同一の国固有照合順序を使用していることを確認する必要があります。これには、テーブルで使用されるグローバルのみならず、IRISTEMP やプロセス・プライベート・グローバルなどの、一時ファイルでインデックスに使用されるグローバルも含まれます。詳細は、"SQL 照合と NLS 照合" を参照してください。