ORDER BY 句 (Transact-SQL)

[アーティクル]
06/18/2013

SQL Server 2012 でクエリによって返されるデータを並べ替えます。この句は次の場合に使用します。

指定した列リストでクエリの結果セットを並べ替え、必要に応じて、返される行を指定の範囲に制限する。 ORDER BY 句が指定されていない限り、結果セットとして返される行の順序は保証されません。
順位付け関数の値が結果セットに適用される順序を決定する。

構文

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
クエリの結果セットの並べ替えに使用する列または式を指定します。並べ替え列は、列の名前、列の別名、または選択リスト内の列の位置を示す負以外の整数で指定できます。

並べ替え列は複数指定できます。列名は一意である必要があります。 ORDER BY 句内に記述する並べ替え列の並び順によって、並べ替えられた結果セットの構成が決まります。つまり、結果セットが最初の列を基準に並べ替えられた後、その並べ替えられたリストが 2 つ目の列を基準に並べ替えられます。それ以降も同様の並べ替えが行われます。

ORDER BY 句で参照されている列名は、選択リスト内の列か、FROM 句で指定したテーブルで定義されている列と正確に対応している必要があります。
COLLATE collation_name
テーブルまたはビューで定義した列の照合順序ではなく、collation_name で指定した照合順序に従って ORDER BY 操作を実行します。 collation_name には、Windows 照合順序名と SQL 照合順序名のいずれかを指定できます。詳細については、「照合順序と Unicode のサポート」を参照してください。 COLLATE は、char、varchar、nchar、および nvarchar 型の列にのみ適用できます。
ASC | DESC
指定した列の値を昇順と降順のどちらで並べ替えるかを指定します。 ASC を指定した場合、最小値から最大値の順序で並べ替えられます。 DESC を指定した場合、最大値から最小値の順序で並べ替えられます。 ASC が既定の並べ替え順序です。 NULL 値は最小値として扱われます。
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
クエリ式から行を取得する前にスキップする行の数を指定します。 0 以上の整数の定数か式を指定できます。

offset_row_count_expression には、変数、パラメーター、または定数スカラーサブクエリを指定できます。サブクエリを使用する場合は、クエリスコープの外部で定義された列は参照できません。つまり、外部のクエリと関連付けることはできません。

ROW と ROWS はシノニムで、ANSI 互換性を確保するために提供されています。

クエリ実行プランでは、オフセット行数値は TOP クエリ演算子の Offset 属性に示されます。
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
OFFSET 句が処理された後に取得する行の数を指定します。 1 以上の整数の定数か式を指定できます。

fetch_row_count_expression には、変数、パラメーター、または定数スカラーサブクエリを指定できます。サブクエリを使用する場合は、クエリスコープの外部で定義された列は参照できません。つまり、外部のクエリと関連付けることはできません。

FIRST と NEXT はシノニムで、ANSI 互換性を確保するために提供されています。

ROW と ROWS はシノニムで、ANSI 互換性を確保するために提供されています。

クエリ実行プランでは、オフセット行数値は TOP クエリ演算子の Rows または Top 属性に示されます。

ベストプラクティス

選択リスト内の列の位置を表すために、ORDER BY 句で整数を指定しないでください。たとえば、SELECT ProductID, Name FROM Production.Production ORDER BY 2 などのステートメントは有効ですが、実際の列名を指定した場合と比べて理解が難しくなります。さらに、列の順序を変更したり、新しい列を追加するなどして、選択リストに変更を加えた場合は、予期しない結果が生じないように ORDER BY 句を変更する必要が生じます。

SELECT TOP (N) ステートメントでは必ず ORDER BY 句を使用してください。これは、TOP の処理対象の行を指定するための唯一の方法です。詳細については、「TOP (Transact-SQL)」を参照してください。

相互運用性

別のソースから行を挿入するときに、ORDER BY 句を SELECT…INTO ステートメントと共に使用する場合、行が指定した順序で挿入されるかどうかは保証されません。

ビューで OFFSET および FETCH を使用しても、ビューの updateability プロパティは変更されません。

制限事項と制約事項

ORDER BY 句で指定できる列の数に制限はありません、ただし、ORDER BY 句で指定した列の合計サイズが 8,060 バイトを超えることはできません。

ORDER BY 句では、ntext、text、image、geography、geometry、xml 型の列は使用できません。

順位付け関数に order_by_expression が使用されている場合、整数または定数は指定できません。詳細については、「OVER 句 (Transact-SQL)」を参照してください。

FROM 句でテーブルの別名を指定している場合、ORDER BY 句でその列を修飾する際に使用できるのは別名だけです。

SELECT ステートメントで次のいずれかの句または演算子を使用する場合は、ORDER BY 句で指定する列の名前と別名が選択リストで定義されている必要があります。

UNION 演算子
EXCEPT 演算子
INTERSECT 演算子
SELECT DISTINCT

これに加えて、ステートメントで UNION、EXCEPT、または INTERSECT 演算子を使用する場合は、列の名前または別名が、最初の (左側の) クエリの選択リストに指定されている必要があります。

UNION、EXCEPT、または INTERSECT 演算子を使用するクエリでは、ステートメントの末尾でのみ ORDER BY を使用できます。この制限が適用されるのは、UNION、EXCEPT、および INTERSECT をサブクエリではなく、最上位レベルのクエリで指定する場合のみです。例については、後の「例」のセクションを参照してください。

TOP 句か、OFFSET 句と FETCH 句が指定されていない場合、ビュー、インライン関数、派生テーブル、およびサブクエリでは ORDER BY 句は無効です。これらのオブジェクトで ORDER BY を使用する場合、この句は TOP 句か、OFFSET 句と FETCH 句で返される行の特定にのみ使用されます。クエリ自体にも ORDER BY を指定しない限り、これらの構造をクエリしたときに、ORDER BY 句で順序どおりの結果が得られるかどうかは保証されません。

インデックス付きビューや、CHECK OPTION 句を使用して定義されたビューでは、OFFSET と FETCH はサポートされません。

TOP と ORDER BY を使用できるクエリでは、OFFSET と FETCH を使用できます。ただし次の制限があります。

OVER 句は OFFSET と FETCH をサポートしていません。
OFFSET と FETCH は、INSERT、UPDATE、MERGE、および DELETE ステートメントでは直接指定できませんが、これらのステートメントで定義されたサブクエリで指定できます。たとえば、INSERT INTO SELECT ステートメントの場合、SELECT ステートメントで OFFSET と FETCH を指定できます。
UNION、EXCEPT、または INTERSECT 演算子を使用するクエリでは、クエリ結果の順序を指定する最後のクエリでのみ OFFSET と FETCH を指定できます。
(同じクエリスコープ内の) 同じクエリ式で TOP を OFFSET および FETCH と組み合わせて使用することはできません。

OFFSET と FETCH を使用した返される行の制限

クエリページングソリューションを実装し、クライアントアプリケーションに送信される行の数を制限するには、TOP 句ではなく OFFSET 句と FETCH 句を使用することをお勧めします。

ページングソリューションとして OFFSET と FETCH を使用する場合は、クライアントアプリケーションに返されるデータの "ページ" ごとにクエリを 1 回実行する必要があります。たとえば、10 行単位でクエリの結果を取得するには、クエリを 1 回実行して行 1 から行 10 を取得した後、クエリをもう一度実行して行 11 から行 20 を取得する必要があります。これ以降も、同様の処理が必要です。各クエリは独立しており、相互に関連付けられることはありません。つまり、クエリを 1 回実行してサーバーで状態を維持するカーソルを使用する場合とは異なり、状態の追跡はクライアントアプリケーションで行われます。 OFFSET と FETCH を使用してクエリ要求の結果に一貫性を持たせるためには、次の条件を満たす必要があります。

クエリで使用される基になるデータが変更されていない。つまり、クエリで処理される行が更新されていないか、クエリからのすべてのページ要求が、スナップショットまたはシリアル化可能なトランザクション分離を使用して単一のトランザクションで実行される必要があります。これらのトランザクション分離レベルの詳細については、「SET TRANSACTION ISOLATION LEVEL (Transact-SQL)」を参照してください。
ORDER BY 句に、一意であることが保証されている列または列の組み合わせが含まれている。

例については、このトピックの「例」セクションの「単一のトランザクションで複数のクエリを実行する」を参照してください。

ページングソリューションにおいて、一貫性のある実行プランが重要な場合は、OFFSET および FETCH のパラメーターに OPTIMIZE FOR クエリヒントを使用することを検討してください。例については、このトピックの「例」セクションの「OFFSET と FETCH の値として式を指定する」を参照してください。 OPTIMZE FOR の詳細については、「クエリヒント (Transact-SQL)」を参照してください。

使用例

カテゴリ	主な構文要素
基本構文	ORDER BY
昇順と降順の指定	DESC および ASC
照合順序の指定	COLLATE
条件に基づく順序の指定	CASE 式
順位付け関数での ORDER BY の使用	順位付け関数
返される行の数の制限	OFFSET および FETCH
UNION、EXCEPT、および INTERSECT と ORDER BY の併用	UNION

基本構文

このセクションの例では、最低限必要な構文を使用して ORDER BY 句の基本機能を示します。

A. 選択リストで定義されている単一の列を指定する

次の例では、数値の ProductID 列を基準に結果セットを並べ替えます。特定の並べ替え順序が指定されていないため、既定の順序 (昇順) が使用されます。

USE AdventureWorks2012;
GO
SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID;

B. 選択リストで定義されていない列を指定する

次の例では、選択リストではなく、FROM 句で指定したテーブルで定義されている列を基準に結果セットを並べ替えます。

USE AdventureWorks2012;
GO
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ListPrice;

C. 別名を並べ替え列として指定する

次の例では、列の別名 SchemaName を並べ替え列として指定します。

USE AdventureWorks2012;
GO
SELECT name, SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
WHERE type = 'U'
ORDER BY SchemaName;

D. 式を並べ替え列として指定する

次の例では、式を並べ替え列として使用します。 DATEPART 関数を使用して式を定義し、従業員の採用年で結果セットを並べ替えます。

USE AdventureWorks2012;
Go
SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY DATEPART(year, HireDate);

昇順と降順の並べ替え順序を指定する

A. 降順を指定する

次の例では、数値列 ProductID を基準に、結果セットを降順で並べ替えます。

USE AdventureWorks2012;
GO
SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID DESC;

B. 昇順を指定する

次の例では、Name 列を基準に、結果セットを昇順で並べ替えます。文字は数字ではなくアルファベット順に並べ替えられます。つまり、10 の位置は、2 の前になります。

USE AdventureWorks2012;
GO
SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY Name ASC ;

C. 昇順と降順の両方を指定する

次の例では、2 つの列を基準に結果セットを並べ替えます。クエリの結果セットは、まず FirstName 列を基準に昇順で並べ替えられた後、LastName 列を基準に降順で並べ替えられます。

USE AdventureWorks2012;
GO
SELECT LastName, FirstName FROM Person.Person
WHERE LastName LIKE 'R%'
ORDER BY FirstName ASC, LastName DESC ;

照合順序の指定

次の例では、ORDER BY 句での照合順序の指定により、クエリ結果が返される順序がどう変化するかを示します。作成されるテーブルには、大文字と小文字、およびアクセントを区別しない照合順序で定義された列が含まれます。大文字と小文字、およびアクセントが混じった値が挿入されます。 ORDER BY 句で照合順序が指定されていないため、最初のクエリでは、値を並べ替えるときに列の照合順序が使用されます。 2 番目のクエリでは、大文字と小文字、およびアクセントを区別する照合順序が ORDER BY 句で指定されているため、行が返される順序が変わります。

USE tempdb;
GO
CREATE TABLE #t1 (name nvarchar(15) COLLATE Latin1_General_CI_AI)
GO
INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(N'sanchez');

-- This query uses the collation specified for the column 'name' for sorting.
SELECT name
FROM #t1
ORDER BY name;
-- This query uses the collation specified in the ORDER BY clause for sorting.
SELECT name
FROM #t1
ORDER BY name COLLATE Latin1_General_CS_AS;

条件に基づく順序の指定

次の例では、ORDER BY 句で CASE 式を使用して、指定された列の値に基づいて、条件に応じて行の並べ替え順序を決定しています。最初の例では、HumanResources.Employee テーブルの SalariedFlag 列の値を評価します。 SalariedFlag が 1 に設定されている従業員は BusinessEntityID の降順で、 SalariedFlag が 0 に設定されている従業員は BusinessEntityID の昇順で返されます。 2 番目の例では、列 CountryRegionName が 'United States' と等しい場合は結果セットが列 TerritoryName によって並べ替えられ、他のすべての列は CountryRegionName によって並べ替えられます。

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

順位付け関数での ORDER BY の使用

次の例では、順序付け関数 ROW_NUMBER、RANK、DENSE_RANK、および NTILE で ORDER BY 句を使用しています。

USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
    ,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

返される行の数の制限

次の例では、OFFSET と FETCH を使用して、クエリによって返される行の数を制限します。

A. OFFSET と FETCH の値として整数の定数を指定する

次の例では、OFFSET および FETCH 句の値として整数の定数を指定します。最初のクエリにより、DepartmentID 列で並べ替えられたすべての行が返されます。このクエリによって返される結果と、後の 2 つのクエリの結果を比べてみてください。次のクエリは、OFFSET 5 ROWS 句を使用して最初の 5 行をスキップし、残りのすべての行を返します。最後のクエリでは、OFFSET 0 ROWS 句を使用して最初の行から処理を開始した後、FETCH NEXT 10 ROWS ONLY を使用して、返される行を、並べ替えられた結果セットからの 10 行に制限します。

USE AdventureWorks2012;
GO
-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID 
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

B. OFFSET と FETCH の値として変数を指定する

次の例では、変数 @StartingRowNumber および @FetchRows を宣言し、これらの変数を OFFSET および FETCH 句で指定しています。

USE AdventureWorks2012;
GO
-- Specifying variables for OFFSET and FETCH values  
DECLARE @StartingRowNumber tinyint = 1
      , @FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC 
    OFFSET @StartingRowNumber ROWS 
    FETCH NEXT @FetchRows ROWS ONLY;

C. OFFSET と FETCH の値として式を指定する

次の例では、@StartingRowNumber - 1 という式を使用して OFFSET 値を指定し、@EndingRowNumber - @StartingRowNumber + 1 という式を使用して FETCH 値を指定しています。さらに、クエリヒントの OPTIMIZE FOR も指定しています。このヒントにより、クエリをコンパイルおよび最適化するときにローカル変数に特定の値を指定できます。この値はクエリを最適化する過程でのみ使用され、クエリの実行時には使用されません。詳細については、「クエリヒント (Transact-SQL)」を参照してください。

USE AdventureWorks2012;
GO

-- Specifying expressions for OFFSET and FETCH values    
DECLARE @StartingRowNumber tinyint = 1
      , @EndingRowNumber tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC 
    OFFSET @StartingRowNumber - 1 ROWS 
    FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY
OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

D. OFFSET と FETCH の値として定数スカラーサブクエリを指定する

次の例では、定数スカラーサブクエリを使用して FETCH 句に値を定義します。サブクエリにより、dbo.AppSettings テーブルの PageSize 列から 1 つの値が返されます。

-- Specifying a constant scalar subquery
USE AdventureWorks2012;
GO
CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);
GO
INSERT INTO dbo.AppSettings VALUES(1, 10);
GO
DECLARE @StartingRowNumber tinyint = 1;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC 
    OFFSET @StartingRowNumber ROWS 
    FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

E. 単一のトランザクションで複数のクエリを実行する

次の例は、クエリのすべての要求で一貫性のある結果が返されるようにするページングソリューションを実装する方法の 1 つを示しています。このクエリは、スナップショット分離レベルを使用して単一のトランザクションで実行されます。また、ORDER BY 句で指定された列により、列の一意性が確保されます。

USE AdventureWorks2012;
GO

-- Ensure the database can support the snapshot isolation level set for the query.
IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0
    ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Set the transaction isolation level  to SNAPSHOT for this query.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

-- Beging the transaction
BEGIN TRANSACTION;
GO
-- Declare and set the variables for the OFFSET and FETCH values.
DECLARE @StartingRowNumber int = 1
      , @RowCountPerPage int = 3;

-- Create the condition to stop the transaction after all rows have been returned.
WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber
BEGIN

-- Run the query until the stop condition is met.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC 
    OFFSET @StartingRowNumber - 1 ROWS 
    FETCH NEXT @RowCountPerPage ROWS ONLY;

-- Increment @StartingRowNumber value.
SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;
CONTINUE
END;
GO
COMMIT TRANSACTION;
GO

UNION、EXCEPT、および INTERSECT と ORDER BY の併用

クエリで UNION、EXCEPT、または INTERSECT 演算子を使用する場合は、ORDER BY 句をステートメントの末尾に指定する必要があります。この場合、結合されたクエリの結果が並べ替えられます。次の例では、赤色または黄色のすべての製品が返され、この結合リストが ListPrice 列を基準に並べ替えられます。

USE AdventureWorks2012;
GO
SELECT Name, Color, ListPrice
FROM Production.Product
WHERE Color = 'Red'
-- ORDER BY cannot be specified here.
UNION ALL
SELECT Name, Color, ListPrice
FROM Production.Product
WHERE Color = 'Yellow'
ORDER BY ListPrice ASC;

次の方法で共有

ORDER BY 句 (Transact-SQL)

構文

引数

ベストプラクティス

相互運用性

制限事項と制約事項

OFFSET と FETCH を使用した返される行の制限

使用例

基本構文

A. 選択リストで定義されている単一の列を指定する

B. 選択リストで定義されていない列を指定する

C. 別名を並べ替え列として指定する

D. 式を並べ替え列として指定する

昇順と降順の並べ替え順序を指定する

A. 降順を指定する

B. 昇順を指定する

C. 昇順と降順の両方を指定する

照合順序の指定

条件に基づく順序の指定

順位付け関数での ORDER BY の使用

返される行の数の制限

A. OFFSET と FETCH の値として整数の定数を指定する

B. OFFSET と FETCH の値として変数を指定する

C. OFFSET と FETCH の値として式を指定する

D. OFFSET と FETCH の値として定数スカラーサブクエリを指定する

E. 単一のトランザクションで複数のクエリを実行する

UNION、EXCEPT、および INTERSECT と ORDER BY の併用

関連項目

参照

その他のリソース

次の方法で共有

ORDER BY 句 (Transact-SQL)

構文

引数

ベスト プラクティス

相互運用性

制限事項と制約事項

OFFSET と FETCH を使用した返される行の制限

使用例

基本構文

A. 選択リストで定義されている単一の列を指定する

B. 選択リストで定義されていない列を指定する

C. 別名を並べ替え列として指定する

D. 式を並べ替え列として指定する

昇順と降順の並べ替え順序を指定する

A. 降順を指定する

B. 昇順を指定する

C. 昇順と降順の両方を指定する

照合順序の指定

条件に基づく順序の指定

順位付け関数での ORDER BY の使用

返される行の数の制限

A. OFFSET と FETCH の値として整数の定数を指定する

B. OFFSET と FETCH の値として変数を指定する

C. OFFSET と FETCH の値として式を指定する

D. OFFSET と FETCH の値として定数スカラー サブクエリを指定する

E. 単一のトランザクションで複数のクエリを実行する

UNION、EXCEPT、および INTERSECT と ORDER BY の併用

関連項目

参照

その他のリソース

ベストプラクティス

D. OFFSET と FETCH の値として定数スカラーサブクエリを指定する