ORDER BY (cláusula de Transact-SQL)

  • Artículo

Ordenar los datos devueltos por una consulta en SQL Server 2012. Use esta cláusula para:

  • Ordenar el conjunto de resultados de una consulta por la lista de columnas especificada y, opcionalmente, limitar las filas devueltas a un intervalo especificado. El orden en que se devuelven las filas en un conjunto de resultados no se puede garantizar, a menos que se especifique una cláusula ORDER BY.

  • Determinar el orden en que se aplican los valores de la función de categoría al conjunto de resultados.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

  • order_by_expression
    Especifica una columna o expresión según la que se ordenará el conjunto de resultados de la consulta. Una columna de ordenación se puede especificar como un nombre o un alias de columna, o un entero no negativo que representa la posición de la columna en la lista de selección.

    Es posible especificar varias columnas de ordenación. Los nombres de columna tienen que ser únicos. La secuencia de las columnas de ordenación de la cláusula ORDER BY define la organización del conjunto de resultados ordenado. Es decir, el conjunto de resultados se ordena conforme a la primera columna y, a continuación, esa lista ordenada se ordena según la segunda columna, y así sucesivamente.

    Los nombres de columna a los que se hace referencia en la cláusula ORDER BY deben corresponderse con una columna de la lista de selección o con una columna definida en la tabla especificada en la cláusula FROM sin ambigüedades.

  • COLLATE collation_name
    Especifica que la operación ORDER BY debe realizarse conforme a la intercalación especificada en collation_name y no conforme a la intercalación de la columna definida en la tabla o vista. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información, vea Compatibilidad con la intercalación y Unicode. COLLATE solo se aplica a las columnas con tipos de datos char, varchar, nchar y nvarchar.

  • ASC | DESC
    Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC ordena del valor mínimo al valor máximo. DESC ordena del valor máximo al valor mínimo. ASC es el criterio de ordenación predeterminado. Los valores NULL se tratan como los valores más bajos posibles.

  • OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    Especifica el número de filas que se deben omitir antes de comenzar a devolver filas de la expresión de consulta. El valor puede ser una expresión o constante entera mayor o igual que cero.

    offset_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

    ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

    En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Offset del operador de consulta TOP.

  • FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    Especifica el número de filas que se devolverán después de procesar la cláusula OFFSET. El valor puede ser una expresión o constante entera mayor o igual que uno.

    fetch_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

    FIRST y NEXT son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

    ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

    En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Rows o Top del operador de consulta TOP.

Prácticas recomendadas

Evite especificar enteros en la cláusula ORDER BY como representaciones posicionales de las columnas en la lista de selección. Por ejemplo, aunque una instrucción como SELECT ProductID, Name FROM Production.Production ORDER BY 2 es válida, otros usuarios no la entenderán tan bien como si especificase el nombre de la columna real. Además, para realizar cambios en la lista de selección, como modificar el orden de las columnas o agregar otras nuevas, será preciso modificar la cláusula ORDER BY a fin de evitar resultados inesperados.

En una instrucción SELECT TOP (N), utilice siempre una cláusula ORDER BY. Esta es la única manera de indicar previsiblemente a qué filas afecta TOP. Para obtener más información, vea TOP (Transact-SQL).

Interoperabilidad

Cuando se utiliza con una instrucción SELECT...INTO para insertar filas de otro origen, la cláusula ORDER BY no garantiza la inserción de las filas en el orden especificado.

Al usar OFFSET y FETCH en una vista no se cambia la propiedad Updateability de la vista.

Limitaciones y restricciones

No hay ningún límite en cuanto al número de columnas de la cláusula ORDER BY; sin embargo, el tamaño total de las columnas especificadas en una cláusula ORDER BY no puede superar los 8.060 bytes.

Las columnas de tipo ntext, text, image, geography, geometry y xml no se pueden usar en una cláusula ORDER BY.

No puede especificarse un entero o una constante cuando en una función de categoría aparece order_by_expression . Para obtener más información, vea OVER (cláusula de Transact-SQL).

Si los nombres de tabla están asociados a un alias en la cláusula FROM, solo pueden usarse los nombres de alias para calificar sus columnas en la cláusula ORDER BY.

Los nombres y alias de columna especificados en la cláusula ORDER BY deben estar definidos en la lista de selección si la instrucción SELECT contiene uno de los operadores o cláusulas siguientes:

  • UNION, operador

  • EXCEPT, operador

  • INTERSECT, operador

  • SELECT DISTINCT

Además, cuando la instrucción incluye un operador UNION, EXCEPT o INTERSECT, los nombres o los alias de columna deben ser los especificados en la lista de selección de la primera consulta (lado izquierdo).

En una consulta que utiliza los operadores UNION, INTERSECT o EXCEPT, ORDER BY se permite únicamente al final de la instrucción. Esta restricción se aplica únicamente cuando se especifica UNION, EXCEPT e INTERSECT en una consulta de nivel superior y no en una subconsulta. Vea la sección Ejemplos que aparece más adelante.

La cláusula ORDER BY no es válida en vistas, funciones insertadas, tablas derivadas y subconsultas, a menos que se especifiquen también las cláusulas TOP u OFFSET y FETCH. Cuando ORDER BY se utiliza en estos objetos, la cláusula únicamente se utiliza para determinar las filas devueltas por la cláusula TOP o las cláusulas OFFSET Y FETCH. La cláusula ORDER BY no garantiza resultados ordenados cuando se consulten estos constructores, a menos que también se especifique ORDER BY en la misma consulta.

OFFSET y FETCH no se admiten en vistas indizadas ni en vistas definidas mediante la cláusula CHECK OPTION.

OFFSET y FETCH se pueden utilizar en cualquier consulta que permita TOP y ORDER BY con las siguientes limitaciones:

  • La cláusula OVER no admite OFFSET ni FETCH.

  • OFFSET y FETCH no se pueden especificar directamente en las instrucciones INSERT, UPDATE, MERGE ni DELETE, pero sí en una subconsulta definida en ellas. Por ejemplo, en la instrucción INSERT INTO SELECT, se pueden especificar OFFSET y FETCH en la instrucción SELECT.

  • En una consulta que utiliza los operadores UNION, EXCEPT o INTERSECT, OFFSET y FETCH únicamente se pueden utilizar en la consulta final que especifica el orden de los resultados de la consulta.

  • TOP no se puede combinar con OFFSET y FETCH en la misma expresión de consulta (en el mismo ámbito de la consulta).

Utilizar OFFSET y FETCH para limitar las filas devueltas

Recomendamos utilizar las cláusulas OFFSET y FETCH en lugar de la cláusula TOP para implementar una solución de paginación de consulta y limitar el número de filas enviadas a una aplicación cliente.

Para utilizar OFFSET y FETCH como solución de paginación, es preciso ejecutar la consulta una vez por cada "página" de datos devuelta a la aplicación cliente. Por ejemplo, para devolver los resultados de una consulta en incrementos de 10 filas, se debe ejecutar la consulta una vez para devolver las filas de 1 a 10, después otra vez para devolver las filas de 11 a 20, y así sucesivamente. Cada consulta es independiente y no está relacionada con las demás de forma alguna. Esto significa que, a diferencia de cuando se usa un cursor en que la consulta se ejecuta una vez y su estado se mantiene en el servidor, en este caso es la aplicación cliente la responsable de realizar el seguimiento del estado. Para lograr resultados estables entre las solicitudes de consultas donde se utilicen OFFSET y FETCH, se deben cumplir las siguientes condiciones:

  1. Los datos subyacentes que la consulta utilice no deben cambiar. Es decir, o bien las filas afectadas por la consulta no se actualizarán, o bien todas las solicitudes correspondientes a las páginas de la consulta se ejecutarán en una transacción única utilizando el aislamiento de transacción serializable o de instantánea. Para obtener información acerca de estos niveles de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

  2. Debe garantizarse que la columna o combinación de columnas contenidas en la cláusula ORDER BY sean únicas.

Vea el ejemplo que "Ejecutar varias consultas en una sola transacción" en la sección Ejemplos que aparece más adelante en este tema.

Si el hecho de que los planes de ejecución sean coherentes es importante para su solución de paginación, puede ser conveniente utilizar la sugerencia de consulta OPTIMIZE FOR para los parámetros de OFFSET y FETCH. Vea "Especificar expresiones para valores de OFFSET y FETCH" en la sección Ejemplos que aparece más adelante en este tema. Para obtener más información acerca de OPTIMIZE FOR, vea Sugerencias de consulta (Transact-SQL).

Ejemplos

Categoría

Elementos de sintaxis ofrecidos

Sintaxis básica

ORDER BY

Especificar orden ascendente y descendente

DESC • ASC

Especificar una intercalación

COLLATE

Especificar un orden condicional

CASE, expresión

Usar ORDER BY en una función de categoría

Funciones de categoría

Limitar el número de filas devueltas

OFFSET • FETCH

Usar ORDER BY con UNION, EXCEPT e INTERSECT

UNION

Sintaxis básica

En los ejemplos de esta sección se muestra la funcionalidad básica de la cláusula ORDER BY utilizando la sintaxis mínima requerida.

A.Especificar una sola columna definida en la lista de selección

En el siguiente ejemplo se ordena el conjunto de resultados por la columna numérica ProductID. Dado que no se especifica un criterio de ordenación concreto, se utiliza el valor predeterminado (orden ascendente).

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

B.Especificar una columna que no está definida en la lista de selección

En el siguiente ejemplo se ordena el conjunto de resultados por una columna que no está incluida en la lista de selección, pero sí definida en la tabla especificada en la cláusula FROM.

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

C.Especificar un alias como columna de ordenación

En el ejemplo siguiente se especifica el alias de columna SchemaName como columna de criterio de ordenación.

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

D.Especificar una expresión como columna de ordenación

En el ejemplo siguiente se utiliza una expresión como columna de ordenación. La expresión se define mediante la función DATEPART para ordenar el conjunto de resultados según el año de contratación de los empleados.

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

Especificar un criterio de ordenación ascendente y descendente

A.Especificar un orden descendente

En el siguiente ejemplo se ordena el conjunto de resultados en sentido descendente según la columna numérica ProductID.

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

B.Especificar un orden ascendente

En el siguiente ejemplo se ordena el conjunto de resultados en orden ascendente según la columna Name. Observe que los caracteres están ordenados alfabéticamente, no numéricamente. Es decir, 10 se ordena antes que 2.

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

C.Especificar orden ascendente y también descendente

En el siguiente ejemplo se ordena el conjunto de resultados según dos columnas. El conjunto de resultados se ordena en primer lugar en sentido ascendente según la columna FirstName y, a continuación, en orden descendente según la columna LastName.

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

Especificar una intercalación

En el siguiente ejemplo se muestra cómo especificar una intercalación en la cláusula ORDER BY puede cambiar el orden en que se devuelven los resultados de la consulta. Se crea una tabla que contiene una columna definida mediante una intercalación que no distingue entre mayúsculas y minúsculas, ni las tildes. Los valores se insertan con diversas diferencias de uso de mayúsculas, minúsculas y tildes. Dado que no se especifica ninguna intercalación en la cláusula ORDER BY, la primera consulta utiliza la intercalación de la columna al ordenar los valores. En la segunda consulta, se especifica una intercalación que distingue entre mayúsculas y minúsculas y las tildes; en consecuencia, cambia el orden en el que se devuelven las filas.

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;

Especificar un orden condicional

En los ejemplos siguientes se utiliza la expresión CASE en una cláusula ORDER BY para determinar de manera condicional el criterio de ordenación de las filas según el valor de una columna dada. En el primer ejemplo se evalúe el valor de la columna SalariedFlag de la tabla HumanResources.Employee. Los empleados que tienen la columna SalariedFlag establecida en 1 se devuelven en orden descendente según el BusinessEntityID. Los empleados que tienen la columna SalariedFlag establecida en 0 se devuelven en orden ascendente según el BusinessEntityID. En el segundo ejemplo, el conjunto de resultados se ordena según la columna TerritoryName cuando la columna CountryRegionName es igual a 'United States' y según la columna CountryRegionName en las demás filas.

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;

Usar ORDER BY en una función de categoría

En el siguiente ejemplo se utiliza la cláusula ORDER BY en las funciones de categoría ROW_NUMBER, RANK, DENSE_RANK y 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;

Limitar el número de filas devueltas

En los siguientes ejemplos se utiliza OFFSET y FETCH para limitar el número de filas devueltas por una consulta.

A.Especificar constantes enteras para los valores de OFFSET y FETCH

En el siguiente ejemplo se especifica una constante entera como valor para las cláusulas OFFSET y FETCH. La primera consulta devuelve todas las filas ordenadas según la columna DepartmentID. Compare los resultados devueltos por esta consulta con los de las dos consultas siguientes. La consulta siguiente utiliza la cláusula OFFSET 5 ROWS para omitir las primeras 5 filas y devolver todas las restantes. La última consulta utiliza la cláusula OFFSET 0 ROWS para comenzar por la primera fila y, a continuación, utiliza FETCH NEXT 10 ROWS ONLY para limitar las filas devueltas a 10 filas del conjunto de resultados ordenado.

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.Especificar variables para los valores de OFFSET y FETCH

En el siguiente ejemplo se declaran las variables @StartingRowNumber y @FetchRows, y se especifican estas variables en las cláusulas OFFSET y 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.Especificar expresiones para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza la expresión @StartingRowNumber - 1 para especificar el valor de OFFSET y la expresión @EndingRowNumber - @StartingRowNumber + 1 para especificar el valor de FETCH. Además, se especifica la sugerencia de consulta OPTIMIZE FOR. Esta sugerencia se puede usar para que se utilice un valor concreto para una variable local al compilar y optimizar la consulta. El valor se utiliza solo durante la optimización de la consulta y no durante la ejecución de la misma. Para obtener más información, vea Sugerencias de consulta (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.Especificar una subconsulta escalar constante para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza una subconsulta escalar constante a fin de definir el valor para la cláusula FETCH. La subconsulta devuelve un valor único de la columna PageSize de la tabla 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.Ejecutar varias consultas en una sola transacción

En el siguiente ejemplo se muestra un método de implementar una solución de paginación que permite asegurarse de la devolución de resultados estables en todas las solicitudes de la consulta. La consulta se ejecuta en una sola transacción utilizando el nivel de aislamiento de instantánea, mientras que la columna especificada en la cláusula ORDER BY asegura la singularidad de la columna.

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

Usar ORDER BY con UNION, EXCEPT e INTERSECT

Cuando una consulta utiliza los operadores UNION, EXCEPT o INTERSECT, la cláusula ORDER BY se debe especificar al final de la instrucción y se ordenan los resultados de las consultas combinadas. En el siguiente ejemplo se devuelven todos los productos que son rojos o amarillos y la lista combinada se ordena según la columna 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;

Vea también

Referencia

Expresiones (Transact-SQL)

SELECT (Transact-SQL)

FROM (Transact-SQL)

Funciones de categoría (Transact-SQL)

TOP (Transact-SQL)

Sugerencias de consulta (Transact-SQL)

EXCEPT e INTERSECT (Transact-SQL)

UNION (Transact-SQL)

CASE (Transact-SQL)