Ordenar y limitar

1. Qué es

Una tabla no tiene orden. Es un conjunto de filas, y el motor las devuelve como le conviene. Si quieres un orden, tienes que pedirlo.

ORDER BY es el último paso del orden-logico-de-ejecucion — el 6 — y LIMIT se aplica todavía después, sobre el resultado ya ordenado. De ahí salen las dos ideas del tema:

• ORDER BY ve el resultado terminado, así que acepta alias y expresiones.
• LIMIT sin ORDER BY es una pregunta mal hecha, porque no hay nada que garantice qué filas te toca.

2. Sintaxis

SELECT
    ...
FROM
    ...
ORDER BY
    columna [ASC | DESC] [NULLS FIRST | NULLS LAST], columna2 ...
LIMIT n
OFFSET m;

• ASC es el valor por defecto y casi nadie lo escribe.
• DESC se aplica a una sola columna, no a las que vienen detrás. ORDER BY a, b DESC ordena a ascendente y b descendente.
• Se puede ordenar por columna, alias, expresión o número de posición.
• OFFSET m salta las primeras m filas.
• La forma del estándar SQL es OFFSET m ROWS FETCH FIRST n ROWS ONLY. PostgreSQL acepta ambas; LIMIT es más corta y es la que se usa.

3. Ejemplos sobre Northwind

Lo básico — los cinco productos más caros:

SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    unit_price DESC
LIMIT 5;

      product_name       | unit_price
-------------------------+------------
 Côte de Blaye           |     263.50
 Thüringer Rostbratwurst |     123.79
 Mishi Kobe Niku         |      97.00
 Sir Rodney's Marmalade  |      81.00
 Carnarvon Tigers        |      62.50

Dos criterios, y el DESC que solo afecta a uno:

SELECT
    country,
    city,
    company_name
FROM
    customers
ORDER BY
    country,
    city DESC
LIMIT 6;

  country  |     city     |        company_name
-----------+--------------+----------------------------
 Argentina | Buenos Aires | Océano Atlántico Ltda.
 Argentina | Buenos Aires | Rancho grande
 Argentina | Buenos Aires | Cactus Comidas para llevar
 Austria   | Salzburg     | Piccolo und mehr
 Austria   | Graz         | Ernst Handel
 Belgium   | Charleroi    | Suprêmes délices

País ascendente (Argentina antes que Austria), ciudad descendente dentro de cada país (Salzburg antes que Graz). Para que las dos fueran descendentes habría que escribir ORDER BY country DESC, city DESC.

La segunda columna solo entra a decidir cuando la primera empata. No son dos ordenamientos separados (uno sobre todo, y luego otro dentro de cada grupo) — es una sola comparación que mira country primero y solo consulta city si dos filas coinciden en país. Si country fuera único por fila, city no tendría ningún empate que resolver y el resultado sería idéntico con o sin ella.

Y fíjate en las tres empresas de Buenos Aires: su orden entre sí no está definido. Empatan en las dos columnas del ORDER BY, así que el motor las coloca como quiere.

3.1 Paginación con OFFSET

OFFSET m salta las primeras m filas del resultado ya ordenado y devuelve a partir de la fila m+1. No es "corta desde la fila m": con OFFSET 5 las filas 1 a 5 se descartan y lo que llega empieza en la fila 6. Y ese conteo de filas es siempre sobre el resultado después de ORDER BY — una tabla no tiene "fila 6" hasta que tú se lo dices con el orden.

-- página 1
SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    unit_price DESC
LIMIT 5;

-- página 2
SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    unit_price DESC
OFFSET 5
LIMIT 5;

     product_name      | unit_price
-----------------------+------------
 Raclette Courdavault  |      55.00
 Manjimup Dried Apples |      53.00
 Tarte au sucre        |      49.30
 Ipoh Coffee           |      46.00
 Rössle Sauerkraut     |      45.60

La página 2 arranca en Raclette Courdavault, que es la fila 6 del resultado ordenado — el OFFSET 5 saltó las 5 primeras (las de la página 1) y no tocó la sexta.

Página n = OFFSET (n-1) * tamaño LIMIT tamaño. Funciona, y tiene dos problemas que conviene saber desde ya:

1. OFFSET no salta gratis. Para darte las filas 100.001 a 100.010, el motor produce y descarta las 100.000 anteriores. En tablas grandes la paginación se vuelve más lenta cuanto más avanzas.
2. Si el orden no es determinista, las páginas se solapan o pierden filas. Ver abajo.

3.2 El empate: por qué LIMIT necesita desempate

Cuatro productos cuestan exactamente 18.00:

SELECT
    product_name,
    unit_price
FROM
    products
WHERE
    unit_price = 18.00;

   product_name   | unit_price
------------------+------------
 Chai             |      18.00
 Steeleye Stout   |      18.00
 Chartreuse verte |      18.00
 Lakkalikööri     |      18.00

Si pides "los 45 productos más caros", el corte cae justo en mitad de ese empate — y esa frontera no la decide el precio (los cuatro valen lo mismo), la decide un orden interno del motor que la consulta nunca pidió:

SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    unit_price DESC
LIMIT 45;

Tramo final del resultado (las primeras 39 filas no tienen empate y no aportan nada nuevo):

           product_name           | unit_price
-----------------------------------+------------
 Gula Malacca                      |      19.45
 Inlagd Sill                       |      19.00
 Chang                             |      19.00
 Boston Crab Meat                  |      18.40
 Chai                              |      18.00
 Steeleye Stout                    |      18.00
(45 filas)

Chai y Steeleye Stout entran; Chartreuse verte y Lakkalikööri se quedan fuera. Y mira Inlagd Sill antes que Chang, aunque los dos valen 19.00 — nada en la consulta pidió ese orden, es lo que le resultó cómodo al motor esta vez. Cuál de los cuatro productos a 18.00 entra al corte tampoco está definido. Puede cambiar al reejecutar, al insertar filas, o al actualizar el motor. Nadie te avisará: cada ejecución devuelve 45 filas y todas parecen bien.

La solución es siempre la misma: añade una columna de desempate única.

SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    unit_price DESC,
    product_id
LIMIT 45;

           product_name           | unit_price
-----------------------------------+------------
 Gula Malacca                      |      19.45
 Chang                             |      19.00
 Inlagd Sill                       |      19.00
 Boston Crab Meat                  |      18.40
 Chai                              |      18.00
 Steeleye Stout                    |      18.00
(45 filas)

Chang (product_id 2) ahora va antes que Inlagd Sill (product_id 36) — y así será siempre, en cualquier equipo, en cualquier versión de Postgres. product_id es la clave primaria, así que el orden pasa a ser único y reproducible. Regla práctica: si un ORDER BY va a alimentar una paginación, un informe que se compara entre semanas, o un LIMIT, termínalo con la clave primaria.

3.3 Los NULL al ordenar

SELECT
    company_name,
    region
FROM
    customers
ORDER BY
    region DESC
LIMIT 4;

            company_name            | region
------------------------------------+--------
 Ana Trujillo Emparedados y helados |
 Antonio Moreno Taquería            |
 Around the Horn                    |

En PostgreSQL el valor por defecto es NULLS LAST en ASC y NULLS FIRST en DESC — los nulos se tratan como si fueran el valor más grande. Se cambia a mano:

ORDER BY region ASC  NULLS FIRST
ORDER BY region DESC NULLS LAST

Escríbelo siempre que haya nulos y el orden importe. Es el punto donde PostgreSQL y SQL Server dan resultados distintos con la misma consulta (sección 5).

3.4 Ordenar texto: el collation

SELECT
    city
FROM
    customers
ORDER BY
    city
LIMIT 8;

Esta consulta no tiene una única respuesta correcta. Depende de dónde la ejecutes:

 en tu PostgreSQL local        en Neon
 (en_US.UTF-8)                 (C.UTF-8)
------------------------      --------------
 Aachen                        Aachen
 Albuquerque                   Albuquerque
 Anchorage                     Anchorage
 Århus                         Barcelona
 Barcelona                     Barquisimeto
 Barquisimeto                  Bergamo
 Bergamo                       Berlin
 Berlin                        Bern

En local, Århus sale cuarto: la Å se ordena como una A. En Neon desaparece del top 8, porque allí el orden es por código de byte y la Å se va detrás de la Z.

No es magia ni un error: es el collation de la base, la regla que decide qué texto va antes que cuál. Y no está en la consulta ni en los datos, sino en la configuración de la base:

    SELECT
        datlocprovider,
        datcollate,
        datlocale
    FROM
        pg_database
    WHERE
        datname = current_database();

Se puede forzar en la propia consulta:

SELECT
    city
FROM
    customers
ORDER BY
    city COLLATE "C"
LIMIT 8;

COLLATE "C" fuerza el orden por byte crudo, sin importar el collation por defecto de la base. Por eso en Neon no cambia nada (ya es su collation por defecto — la consulta con y sin COLLATE "C" da idéntico resultado). En tu local sí cambia, y reproduce el comportamiento de Neon: Å deja de tratarse como una A y Århus se va al final, detrás de la Z, en vez de aparecer cuarto.

Si un informe ordenado alfabéticamente sale distinto en dos servidores, el collation es el primer sospechoso. Es un tema con suficiente enjundia como para tener el suyo propio, desarrollado en el vault: temas/sql/collation-y-orden-del-texto.

3.5 Ordenar por algo que no está en el SELECT

Es legal y a veces útil:

SELECT
    product_name
FROM
    products
ORDER BY
    unit_price DESC
LIMIT 3;

Devuelve solo los nombres, ordenados por un precio que no se ve. Cómodo, y peligroso en un informe: quien lo lea no puede verificar el orden. Si el criterio importa, enséñalo.

4. Dónde encaja en el orden de ejecución

ORDER BY es el paso 6, el último de las cláusulas básicas, y LIMIT se aplica después de él. Es decir: cuando le llega el turno a ORDER BY, el resultado ya está construido — el SELECT ocurrió en el paso 5. Eso lo cambia todo.

4.1 ORDER BY sí acepta un nombre puesto con AS

Al revés que el WHERE:

SELECT
    product_name,
    unit_price * 1.18 AS precio_final
FROM
    products
ORDER BY
    precio_final DESC;

No da error, y no es una incoherencia del motor: cuando se ordena, precio_final ya existe, porque se creó en el paso anterior. En el WHERE (paso 2) todavía no existía. Misma regla, distinto momento.

4.2 Y acepta el número de posición

Por lo mismo — ve las columnas de salida ya numeradas:

SELECT
    product_name,
    unit_price
FROM
    products
ORDER BY
    2 DESC
LIMIT 3;

      product_name       | unit_price
-------------------------+------------
 Côte de Blaye           |     263.50
 Thüringer Rostbratwurst |     123.79
 Mishi Kobe Niku         |      97.00

2 es la segunda columna del SELECT. Cómodo para probar, frágil para guardar: el día que alguien añade una columna al SELECT, el orden cambia solo.

4.3 LIMIT es lo último de lo último, y por eso no acelera nada

Es la confusión más cara del tema. LIMIT 10 no le dice al motor "trae solo 10 filas": le dice "haz todo el trabajo y luego dame 10". Antes de cortar, el motor ya escaneó, filtró, agrupó y —sobre todo— ordenó el conjunto entero.

LIMIT 10 sobre un ORDER BY de un millón de filas no evita ordenar el millón. Reduce lo que se te envía, no lo que se trabaja.

Para que una consulta sea rápida hay que atacar el principio del embudo (el WHERE, paso 2), no el final. Con un índice sobre la columna de orden el motor a veces sí puede saltarse la ordenación completa, y eso se ve en indices y en explain-y-explain-analyze.

5. Errores comunes

1. LIMIT sin ORDER BY. No es "las primeras filas": es "unas filas cualesquiera". Puede cambiar entre ejecuciones.
2. ORDER BY con empates y sin desempate. El caso de los cuatro productos a 18.00.
3. Creer que DESC afecta a todas las columnas. Afecta solo a la suya.
4. Ordenar por número de posición en código que se guarda. ORDER BY 2 es cómodo para probar; el día que alguien añade una columna al SELECT, el orden cambia solo y en silencio.
5. Esperar que LIMIT 10 haga rápida una consulta lenta. Es el último paso: antes ya se ordenó todo. Ver la sección 4 de este mismo tema.
6. Paginar con OFFSET sobre un orden no determinista. La fila 10 de la página 1 puede repetirse como fila 1 de la página 2.
7. Ordenar un número guardado como texto. '10' < '9' es cierto en texto. Si una columna de importes está en varchar, el orden será alfabético y parecerá aleatorio.

6. Diferencias con SQL Server

La fila de los nulos es la que rompe migraciones. La misma consulta, los mismos datos, y las filas sin región salen arriba en un motor y abajo en el otro. Si alguien compara los dos informes fila a fila, no cuadran — y la causa no está a la vista en la consulta.

Detalle de entrevista: en SQL Server, TOP sin ORDER BY no garantiza nada, exactamente igual que LIMIT. Es la misma trampa con otro nombre.

7. Ejercicios

1. Los 10 pedidos con mayor flete, del más caro al más barato, mostrando id, cliente y flete.
2. Clientes ordenados por país y, dentro de cada país, por ciudad — ambos ascendentes — y con los que no tienen región primero.
3. Los productos ordenados de más caro a más barato, garantizando que dos ejecuciones dan exactamente el mismo orden.
4. La segunda página de empleados, con páginas de 3, ordenados por apellido.
5. Sin ejecutarla, di qué problema tiene: sql SELECT customer_id, order_date FROM orders ORDER BY order_date DESC LIMIT 10;
6. Sin ejecutarla, di cuántas filas devuelve, cuáles y por qué: sql SELECT ship_city FROM orders WHERE ship_country = 'Brazil' GROUP BY ship_city LIMIT 3;
7. Explica en una línea por qué esto no da error, aunque precio_final no sea una columna de la tabla: sql SELECT product_name, unit_price * 1.18 AS precio_final FROM products ORDER BY precio_final DESC;

8. Pregunta de negocio

Dirección quiere "el top 10 de productos más caros" para una presentación, y lo va a repetir cada trimestre para comparar. ¿Qué consulta entregas y qué le adviertes?