Clausola ORDER BY (Transact-SQL)

Articolo
06/18/2013

Ordina i dati restituiti da una query in SQL Server 2012. Utilizzare questa clausola per effettuare le operazioni seguenti:

Ordinare il set di risultati di una query in base all'elenco di colonne specificato e, facoltativamente, limitare le righe restituite a un intervallo specificato. L'ordine in cui le righe vengono restituite in un set di risultati non è garantito, a meno che non venga specificata una clausola ORDER BY.
Determinare l'ordine in cui i valori della funzione di rango vengono applicati al set di risultati.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

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
    ]
}

Argomenti

order_by_expression
Specifica una colonna o un'espressione sulla quale ordinare il set di risultati della query. Per specificare una colonna di ordinamento è possibile utilizzare il nome, l'alias di colonna o un intero non negativo che rappresenta la posizione della colonna nell'elenco di selezione.

È possibile specificare più colonne di ordinamento. I nomi delle colonne devono essere univoci. La sequenza delle colonne di ordinamento nella clausola ORDER BY definisce l'organizzazione del set di risultati ordinato. Ciò significa che il set di risultati viene ordinato in base alla prima colonna e che l'elenco ordinato viene ordinato in base alla seconda colonna e così via.

I nomi delle colonne a cui viene fatto riferimento nella clausola ORDER BY devono corrispondere senza ambiguità a una colonna nell'elenco di selezione o a una colonna definita in una tabella specificata nella clausola FROM.
COLLATE collation_name
Specifica che l'operazione ORDER BY deve essere eseguita in base alle regole di confronto indicate in collation_name e non in base alle regole della colonna definite nella tabella o nella vista. collation_name può essere un nome di regole di confronto di Windows o SQL. Per ulteriori informazioni, vedere Regole di confronto e supporto Unicode. Il parametro COLLATE può essere applicato solo alle colonne di tipo char, varchar, nchar e nvarchar.
ASC | DESC
Specifica che i valori nella colonna specificata devono essere ordinati in ordine crescente o decrescente. ASC consente di ordinare i valori dal più piccolo al più grande. DESC consente di ordinare i valori dal più grande al più piccolo. ASC è l'ordinamento predefinito. I valori Null vengono considerati i valori in assoluto più piccoli.
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifica il numero di righe da ignorare prima della restituzione delle righe dall'espressione di query. Il valore può essere un valore costante intero o un'espressione maggiore o uguale a zero.

offset_row_count_expression può essere una variabile, un parametro o una sottoquery scalare costante. Quando viene utilizzata una sottoquery, non è possibile fare riferimento alle colonne definite nell'ambito di query esterno. Ciò significa che non è possibile metterla in correlazione con la query esterna.

ROW e ROWS sono sinonimi e vengono specificati per garantire la compatibilità ANSI.

Nei piani di esecuzione della query, il valore del conteggio delle righe di offset viene visualizzato nell'attributo Offset dell'operatore di query TOP.
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
Specifica il numero di righe da restituire dopo l'elaborazione della clausola OFFSET. Il valore può essere un valore costante intero o un'espressione maggiore o uguale a uno.

fetch_row_count_expression può essere una variabile, un parametro o una sottoquery scalare costante. Quando viene utilizzata una sottoquery, non è possibile fare riferimento alle colonne definite nell'ambito di query esterno. Ciò significa che non è possibile metterla in correlazione con la query esterna.

FIRST e NEXT sono sinonimi e vengono specificati per garantire la compatibilità ANSI.

ROW e ROWS sono sinonimi e vengono specificati per garantire la compatibilità ANSI.

Nei piani di esecuzione della query, il valore del conteggio delle righe di offset viene visualizzato nell'attributo Rows o Top dell'operatore di query TOP.

Procedure consigliate

Evitare di specificare numeri interi nella clausola ORDER BY come rappresentazioni posizionali delle colonne nell'elenco di selezione. Ad esempio, anche se un'istruzione quale SELECT ProductID, Name FROM Production.Production ORDER BY 2 è valida, l'istruzione non risulta comprensibile come la specifica del nome della colonna effettivo. Inoltre, le modifiche all'elenco di selezione, ad esempio la modifica dell'ordine delle colonne o l'aggiunta di nuove colonne, richiederanno la modifica della clausola ORDER BY per evitare risultati imprevisti.

In un'istruzione SELECT TOP (N) utilizzare sempre una clausola ORDER BY. È l'unico modo per indicare prevedibilmente quali righe sono interessate dalla clausola TOP. Per ulteriori informazioni, vedere TOP (Transact-SQL).

Interoperabilità

Se utilizzata insieme a un'istruzione SELECT…INTO per l'inserimento di righe da un'altra origine, la clausola ORDER BY non garantisce l'inserimento delle righe nell'ordine specificato.

L'utilizzo di OFFSET e FETCH in una vista non modifica la proprietà updateability della vista.

Limitazioni e restrizioni

Non vi è limite al numero di colonne nella clausola ORDER BY. Tuttavia, la dimensione totale delle colonne specificata in una clausola ORDER BY non può superare 8.060 byte.

Le colonne di tipo ntext, text, image, geography, geometry e xml non possono essere utilizzate in una clausola ORDER BY.

Non è possibile specificare un valore intero o una costante quando order_by_expression è incluso in una funzione di rango. Per ulteriori informazioni, vedere Clausola OVER (Transact-SQL).

Se per un nome di tabella sono stati creati alias nella clausola FROM, è possibile utilizzare solo il nome dell'alias per qualificare le relative colonne nella clausola ORDER BY.

I nomi e gli alias delle colonne specificati nella clausola ORDER BY devono essere definiti nell'elenco di selezione se l'istruzione SELECT contiene una delle clausole o operatori seguenti:

operatore UNION
operatore EXCEPT
operatore INTERSECT
SELECT DISTINCT

Inoltre, quando l'istruzione include un operatore UNION, EXCEPT o INTERSECT, i nomi o gli alias delle colonne devono essere specificati nell'elenco di selezione della prima query (lato sinistro).

In una query che utilizza l'operatore UNION, EXCEPT o INTERSECT, la clausola ORDER BY è consentita solo alla fine dell'istruzione. Questa restrizione si applica unicamente ai casi in cui UNION, EXCEPT e INTERSECT vengono specificati in una query di livello principale e non in una sottoquery. Vedere la sezione Esempi riportata di seguito.

La clausola ORDER BY non è valida nelle viste, nelle funzioni inline, nelle tabelle derivate e nelle sottoquery, a meno che non vengano specificate anche la clausola TOP o le clausole OFFSET e FETCH. Quando si utilizza ORDER BY in questi oggetti, la clausola viene utilizzata esclusivamente per determinare le righe restituite dalla clausola TOP o dalle clausole OFFSET e FETCH. La clausola ORDER BY non garantisce risultati ordinati in caso di query su questi costrutti, a meno che tale clausola non venga specificata anche nella query.

OFFSET e FETCH non sono supportate nelle viste indicizzate o in una vista definita tramite la clausola CHECK OPTION.

OFFSET e FETCH possono essere utilizzate in qualsiasi query in cui le clausole TOP e ORDER BY siano consentite con le limitazioni seguenti:

La clausola OVER non supporta OFFSET e FETCH.
Non è possibile specificare OFFSET e FETCH direttamente nelle istruzioni INSERT, UPDATE, MERGE e DELETE, ma possono essere specificate in una sottoquery definita in tali istruzioni. Ad esempio, nell'istruzione INSERT INTO SELECT, è possibile specificare OFFSET e FETCH nell'istruzione SELECT.
In una query che utilizza l'operatore UNION, EXCEPT o INTERSECT, è possibile specificare OFFSET e FETCH solo nella query finale che specifica l'ordine dei risultati della query.
Non è possibile combinare TOP con OFFSET e FETCH nella stessa espressione di query (nello stesso ambito della query).

Utilizzo di OFFSET e FETCH per limitare le righe restituite

È consigliabile utilizzare le clausole OFFSET e FETCH anziché la clausola TOP per implementare una soluzione di paging di query e limitare il numero di righe inviate a un'applicazione client.

L'utilizzo di OFFSET e FETCH come soluzione di paging richiede di eseguire la query una volta per ogni "pagina" di dati restituita all'applicazione client. Ad esempio, per restituire i risultati di una query in incrementi di 10 righe, è necessario eseguire la query una volta per restituire le righe da 1 a 10 e quindi eseguirla nuovamente per restituire le righe da 11 a 20 e così via. Ogni query è indipendente e non correlata in alcun modo all'altra. Ciò significa che, contrariamente all'utilizzo di un cursore in cui viene eseguita la query e lo stato viene gestito nel server, l'applicazione client è responsabile del rilevamento dello stato. Per ottenere risultati stabili tra richieste di query utilizzando OFFSET e FETCH, è necessario soddisfare le condizioni seguenti:

I dati sottostanti utilizzati dalla query non devono cambiare. Ciò significa che le righe interessate dalla query non vengono aggiornate o che tutte le richieste di pagine dalla query vengono eseguite in una singola transazione utilizzando l'isolamento dello snapshot o della transazione serializzabile. Per ulteriori informazioni su questi livelli di isolamento delle transazioni, vedere SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
La clausola ORDER BY contiene una colonna o una combinazione di colonne di cui è garantita l'univocità.

Vedere l'esempio "Esecuzione di più query in una singola transazione" nella sezione Esempi più avanti in questo argomento.

Se piani di esecuzione coerenti sono importanti nella soluzione di paging, si consideri l'utilizzo dell'hint per la query OPTIMIZE FOR per i parametri OFFSET e FETCH. Vedere "Specifica di espressioni per valori OFFSET e FETCH" nella sezione Esempi più avanti in questo argomento. Per ulteriori informazioni su OPTIMZE FOR, vedere Hint per la query (Transact-SQL).

Esempi

Categoria	Elementi di sintassi inclusi
Sintassi di base	ORDER BY
Specifica dell'ordine crescente e decrescente	DESC • ASC
Specifica di regole di confronto	COLLATE
Specifica di un ordine condizionale	Espressione CASE
Utilizzo di ORDER BY in una funzione di rango	Funzioni di rango
Limitazione del numero di righe restituite	OFFSET • FETCH
Utilizzo di ORDER BY con UNION, EXCEPT e INTERSECT	UNION

Sintassi di base

Negli esempi contenuti in questa sezione vengono illustrate le funzionalità di base della clausola ORDER BY utilizzando la sintassi minima necessaria.

A.Specifica di una singola colonna definita nell'elenco di selezione

Nell'esempio seguente il set di risultati viene ordinato in base alla colonna ProductID numerica. Poiché non viene specificato un ordinamento specifico, viene utilizzata l'impostazione predefinita (ordine crescente).

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

B.Specifica di una colonna non definita nell'elenco di selezione

Nell'esempio seguente il set di risultati viene ordinato in base a una colonna non inclusa nell'elenco di selezione, ma viene definito nella tabella specificata nella clausola FROM.

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

C.Specifica di un alias come colonna di ordinamento

Nell'esempio seguente l'alias della colonna SchemaName viene specificato come colonna di ordinamento.

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

D.Specifica di un'espressione come colonna di ordinamento

Nell'esempio seguente viene utilizzata un'espressione come colonna di ordinamento. L'espressione viene definita tramite la funzione DATEPART per ordinare il set di risultati in base all'anno in cui sono stati assunti i dipendenti.

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

Specifica dell'ordinamento crescente e decrescente

A.Specifica di un ordine decrescente

Nell'esempio seguente il set di risultati viene ordinato in base alla colonna numerica ProductID in ordine decrescente.

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

B.Specifica di un ordine crescente

Nell'esempio seguente il set di risultati viene ordinato in base alla colonna Name in ordine crescente. Si noti che i caratteri sono ordinati alfabeticamente, non numericamente. Di conseguenza, 10 precede 2.

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

C.Specifica di entrambi gli ordini, crescente e decrescente

Nell'esempio seguente il set di risultati viene ordinato in base a due colonne. Il set di risultati della query viene ordinato prima in ordine crescente in base alla colonna FirstName e quindi in ordine decrescente in base alla colonna LastName.

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

Specifica di regole di confronto

Nell'esempio seguente viene illustrato come la specifica di regole di confronto nella clausola ORDER BY possa modificare l'ordine in cui vengono restituiti i risultati della query. Viene creata una tabella contenente una colonna definita tramite regole di confronto che non fanno distinzione tra maiuscole e minuscole e tra i vari accenti. I valori vengono inseriti con una varietà di differenze di caso e accento. Poiché nella clausola ORDER BY non vengono specificate regole di confronto, la prima query utilizza le regole di confronto della colonna quando si ordinano i valori. Nella seconda query, nella clausola ORDER BY, vengono specificate regole di confronto che fanno distinzione tra maiuscole e minuscole e tra gli accenti che comportano la modifica dell'ordine in cui vengono restituite le righe.

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;

Specifica di un ordine condizionale

Negli esempi seguenti viene utilizzata l'espressione CASE in una clausola ORDER BY per determinare in modo condizionale l'ordinamento delle righe in base a un valore di colonna specificato. Nel primo esempio, viene calcolato il valore nella colonna SalariedFlag della tabella HumanResources.Employee. I dipendenti per cui SalariedFlag è impostato su 1 vengono restituiti in ordine decrescente in base a BusinessEntityID. I dipendenti per cui SalariedFlag è impostato su 0 vengono restituiti in ordine crescente in base a BusinessEntityID. Nel secondo esempio il set di risultati viene ordinato in base alla colonna TerritoryName quando la colonna CountryRegionName è uguale a "Stati Uniti" e in base a CountryRegionName per tutte le altre righe.

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;

Utilizzo di ORDER BY in una funzione di rango

Nell'esempio seguente la clausola ORDER BY viene utilizzata nelle funzioni di rango ROW_NUMBER, RANK, DENSE_RANK e NTILE.

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;

Limitazione del numero di righe restituite

Negli esempi seguenti vengono utilizzate le clausole OFFSET e FETCH per limitare il numero di righe restituite da una query.

A.Specifica di valori costanti interi per OFFSET e FETCH

Nell'esempio seguente viene specificata una costante intera come valore per le clausole OFFSET e FETCH. La prima query restituisce tutte le righe ordinate in base alla colonna DepartmentID. Confrontare i risultati restituiti da questa query ai risultati delle due query che la seguono. La query successiva utilizza la clausola OFFSET 5 ROWS per ignorare le prime 5 righe e restituire tutte le righe rimanenti. La query finale utilizza la clausola OFFSET 0 ROWS per iniziare con la prima riga, quindi utilizza FETCH NEXT 10 ROWS ONLY per limitare le righe restituite a 10 righe dal set di risultati ordinato.

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.Specifica di variabili per i valori OFFSET e FETCH

Nell'esempio seguente vengono dichiarate le variabili @StartingRowNumber e @FetchRows che vengono in seguito specificate nelle clausole OFFSET e 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.Specifica di espressioni per i valori OFFSET e FETCH

Nell'esempio seguente vengono utilizzate l'espressione @StartingRowNumber - 1 per specificare il valore OFFSET e l'espressione @EndingRowNumber - @StartingRowNumber + 1 per specificare il valore FETCH. Viene inoltre specificato l'hint per la query OPTIMIZE FOR. Questo hint può essere utilizzato per specificare un valore per una variabile locale quando la query viene compilata e ottimizzata. Il valore viene utilizzato solo durante l'ottimizzazione della query e non durante l'esecuzione. Per ulteriori informazioni, vedere Hint per la query (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.Specifica di una sottoquery scalare costante per valori OFFSET e FETCH

Nell'esempio seguente viene utilizzata una sottoquery scalare costante per definire il valore per la clausola FETCH. La sottoquery restituisce un singolo valore dalla colonna PageSize nella tabella dbo.AppSettings.

-- 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.Esecuzione di più query in una singola transazione

Nell'esempio seguente viene illustrato un metodo per l'implementazione di una soluzione di paging che garantisca la restituzione di risultati stabili in tutte le richieste dalla query. La query viene eseguita in una singola transazione utilizzando il livello di isolamento dello snapshot e la colonna specificata nella clausola ORDER BY garantisce l'univocità della colonna.

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

Utilizzo di ORDER BY con UNION, EXCEPT e INTERSECT

Quando una query utilizza l'operatore UNION, EXCEPT o INTERSECT, è necessario specificare la clausola ORDER BY alla fine dell'istruzione e i risultati delle query combinate vengono ordinati. Nell'esempio seguente vengono restituiti tutti i prodotti rossi o gialli e questo elenco combinato viene ordinato in base alla colonna 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;