Desde la primera lección del módulo venimos repitiendo la misma advertencia: el conjunto de resultados no tiene orden garantizado. Ha llegado el momento de tomar el control. ORDER BY es la única forma de que una consulta devuelva las filas en un orden concreto, y aunque parezca la cláusula más sencilla de SQL, esconde media docena de detalles que separan una consulta que funciona por casualidad de una que funciona siempre: qué pasa con los empates, dónde van los nulos, por qué la Ñ puede aparecer en un sitio raro, y por qué ordenar por el número de columna es una bomba de relojería.

1. Sin ORDER BY no hay orden garantizado
2. ASC y DESC
3. Ordenar por varias columnas y resolver empates
4. Ordenar por alias, por expresión y por posición ordinal
5. Los nulos: NULLS FIRST y NULLS LAST
6. Ordenar texto: colación y acentos
7. Ordenar fechas
8. ORDER BY en el orden lógico de ejecución
9. Errores Comunes y Consejos
10. Ejercicios
11. Conclusión

Vamos a hacerlo explícito de una vez. Esta consulta:

devuelve, hoy, en tu equipo, los productos del 1 al 20. Y eso te da una falsa sensación de seguridad, porque el estándar SQL no promete ningún orden cuando no hay ORDER BY, y PostgreSQL tampoco. Lo que ves es el orden en que el motor encontró las filas al recorrer el fichero de datos.

Circunstancias reales en las que ese orden cambia sin que tú toques la consulta:

Puedes comprobar el primer caso tú mismo, si te atreves a modificar los datos (recuerda que el script de 01-06 es idempotente y puedes recargarlo):

El producto 1 aparecerá ahora al final de la lista. Nada ha fallado, nada avisa: simplemente el orden nunca estuvo garantizado.

Regla absoluta: si el orden de las filas importa para tu informe, tu aplicación o tu paginación, escribe ORDER BY. No hay atajos, no hay excepciones, y "es que siempre sale bien" no es un argumento.

ORDER BY se escribe al final de la consulta e indica por qué columna ordenar y en qué sentido:

El catálogo completo de más caro a más barato. Ahí tienes ya una respuesta de negocio: el matcha es el producto más caro (22.00 €) y el tomate triturado el más barato (1.95 €).

Como ASC es el valor por defecto, estas dos son idénticas:

Escribir ASC explícitamente no es obligatorio, pero en consultas con varias columnas y sentidos mezclados ayuda mucho a la lectura.

Cuando dos filas tienen el mismo valor en la columna de ordenación, su orden relativo no está definido. Es el mismo problema de la sección 1, en pequeño.

Las cinco filas de la categoría 1 salen juntas, sí, pero ¿en qué orden entre ellas? El que el motor tenga a bien. Para fijarlo se añaden más columnas al ORDER BY, separadas por comas: la segunda desempata la primera, la tercera desempata la segunda, y así sucesivamente. Cada columna puede llevar su propio ASC o DESC.

Un catálogo perfectamente presentable: agrupado por categoría y, dentro de cada una, del artículo más caro al más barato.

Tres columnas, con datos de clientes:

Léelo de fuera adentro: primero todos los españoles, dentro de ellos las ciudades en orden alfabético, y dentro de cada ciudad los apellidos. Los dos clientes de Barcelona quedan ordenados por apellido (Belmonte antes que Bosch) y los cuatro de Valencia también (Carrasco, Ferrer, Llorens, Martínez).

Consejo profesional: termina siempre el ORDER BY con una columna única. Añadir , id como último criterio garantiza que el orden sea determinista: la misma consulta devuelve exactamente el mismo orden hoy y dentro de un año. Es imprescindible para la paginación, que verás en 02-06, y para que las pruebas automatizadas no fallen aleatoriamente.

Aquí se cobra la deuda de 02-02: como ORDER BY se ejecuta después de SELECT, el alias ya existe y se puede usar.

Observa el empate real en la fila 13 y 14: el bálsamo labial (producto 9) y el zumo de naranja (producto 17) dejan exactamente 2.80 € de margen. El , id final es lo que hace que el 9 salga antes que el 17 de forma reproducible; sin él, el orden entre ambos sería cosa del motor.

Y fíjate en que hemos ordenado por id sin proyectarlo. Eso es perfectamente legal (a diferencia de lo que ocurría con SELECT DISTINCT en 02-04): ORDER BY puede usar cualquier columna de las tablas del FROM, aparezca o no en el resultado.

También puedes repetir la expresión completa en lugar de usar el alias. El resultado es idéntico:

(5 primeras de 20 filas.)

Este es el ranking por margen porcentual, no por margen absoluto, y da un resultado muy distinto: el cepillo de bambú, que solo deja 2.30 € por unidad, es el producto que más porcentaje de margen aporta (65.7 %). Ordenar por la métrica correcta importa tanto como calcularla bien.

SQL permite ordenar por el número de columna dentro del SELECT:

Equivale a ORDER BY precio DESC, porque precio es la segunda columna de la lista.

Funciona, es estándar y está muy extendido en consultas rápidas y exploratorias. Pero es frágil y no debe llegar a código que se guarde:

Regla del curso: ORDER BY 2 para explorar en psql; nombre de columna o alias en cualquier consulta que vayas a guardar.

Un matiz que sorprende: el ordinal solo funciona en ORDER BY (y en GROUP BY, módulo 4). En WHERE no significa nada, y ORDER BY 2 + 1 no ordena por la tercera columna: PostgreSQL entiende que ahí hay una expresión constante (el número 3) y la ignora como criterio de ordenación, porque vale lo mismo en todas las filas.

Un NULL no es mayor ni menor que nada: es desconocido. Pero para ordenar hay que colocarlo en algún sitio, así que cada motor toma una decisión arbitraria. PostgreSQL considera que NULL es el valor más grande.

De ahí salen sus valores por defecto:

Veámoslo con pedidos.empleado_id, que es NULL en los diez pedidos que entraron por la web:

Los diez pedidos gestionados por comerciales (4, 5 y 6) primero, y los diez pedidos web al final. Si quieres los nulos arriba, lo pides explícitamente:

Ahora los pedidos 1, 3, 5, 7, 9, 11, 13, 15, 17 y 19 encabezan la lista, seguidos por los del comercial 4, luego el 5 y luego el 6.

Lo mismo con clientes.referido_por_id:

Los siete clientes que llegaron por su cuenta arriba, y debajo los ocho que vinieron por recomendación, agrupados por quién los trajo: Lucía ha traído a tres (Carlos, Marta e Inés), lo que la convierte en la mejor prescriptora de TiendaVerde.

Esta es una de las diferencias de dialecto que más quebraderos de cabeza da al migrar consultas:

Es decir: la misma consulta ORDER BY empleado_id pone los diez pedidos web al final en PostgreSQL y al principio en MySQL. Si tu informe muestra las diez primeras filas, verás datos completamente distintos.

En los motores que no admiten NULLS FIRST/LAST se emula con una columna auxiliar de ordenación, aprovechando que un booleano ordena false antes que true:

La expresión empleado_id IS NULL vale false (0) para las filas con valor y true (1) para las nulas, así que ordenar por ella empuja los nulos al final. Es un truco que conviene tener en el bolsillo.

Ordenar números o fechas es aritmética. Ordenar texto es cultura, y ahí las cosas se ponen interesantes.

Una colación (collation) es el conjunto de reglas que decide si 'Ñora' va antes o después de 'Oliva', si 'café' y 'CAFE' se consideran iguales al ordenar, y si 'ch' cuenta como una letra o como dos. Cada base de datos PostgreSQL se crea con una colación por defecto, que puedes consultar:

Los dos escenarios habituales:

Puedes comprobarlo en tu instalación con una comparación directa:

En una base creada con colación lingüística (lo habitual):

'Ñora' < 'Oliva' es verdadero con reglas lingüísticas —la Ñ va antes que la O— y falso con la colación C, porque en UTF-8 la Ñ se codifica con dos bytes que empiezan por 0xC3, muy por encima del 0x4F de la O. La misma consulta, dos órdenes distintos, según cómo se creara la base de datos.

Para forzar una colación concreta en una consulta:

COLLATE "es-ES-x-icu" aplica las reglas del español según la biblioteca ICU, que PostgreSQL incluye desde la versión 10. Fíjate en Moreau antes que Moreira: comparten More, y luego a va antes que i.

Puedes ver las colaciones disponibles en tu servidor con:

Notas prácticas sobre colaciones:

• La colación afecta también a WHERE, a LIKE y a los índices. Un índice creado con una colación no sirve para ordenar con otra (módulo 8).
• Ordenar con COLLATE explícito es más lento que usar la colación por defecto, porque impide reutilizar el índice.
• Cambiar la colación de una base existente es una operación mayor: hay que reconstruir todos los índices de texto. Es una decisión que se toma al crear la base de datos.
• Si necesitas un orden "sin acentos ni mayúsculas", la vía moderna en PostgreSQL son las colaciones no deterministas (CREATE COLLATION ... deterministic = false); la vía clásica es ordenar por LOWER(unaccent(columna)), con funciones del módulo 6.

Las fechas ordenan cronológicamente, sin sorpresas, porque el tipo DATE es un número por dentro:

(10 primeras de 20 filas.)

Los pedidos del más reciente al más antiguo: el orden natural de cualquier bandeja de trabajo. En este conjunto de datos la fecha crece con el id, así que ordenar por fecha descendente coincide con ordenar por id descendente; en una base real eso no tiene por qué cumplirse (un pedido puede grabarse con fecha retroactiva) y confiar en ello sería un error.

Aviso importante: esto funciona porque las columnas son de tipo DATE. Si una fecha estuviera guardada como texto, el orden sería alfabético:

Es una razón más para guardar las fechas en columnas de tipo fecha, como se decidió en 01-04 y 01-06.

ORDER BY es el penúltimo paso: se ejecuta después de que las filas estén filtradas, proyectadas y deduplicadas, y solo LIMIT viene después.

Consecuencias que ya has visto en acción:

Y una consideración de rendimiento que retomarás en el módulo 8: ordenar cuesta. PostgreSQL puede evitar el trabajo si existe un índice que ya devuelve las filas en el orden pedido; si no, tiene que ordenar todo el conjunto en memoria (o en disco, si no cabe). Un ORDER BY sobre una tabla de millones de filas sin índice adecuado es una de las causas más frecuentes de consultas lentas.

Un último apunte: si una consulta con ORDER BY se usa como subconsulta (módulo 7) o dentro de una vista (módulo 10), ese orden no se propaga necesariamente a la consulta exterior. El ORDER BY que manda es el del nivel más externo.

• Confiar en el orden sin ORDER BY. Funciona hasta el día que no. Es el error más caro de esta lección.
• Olvidar el desempate. Dos filas con el mismo valor pueden salir en cualquier orden, y ese orden puede cambiar entre ejecuciones. Termina siempre con una columna única.
• Poner DESC una sola vez creyendo que afecta a toda la lista. ORDER BY a, b DESC ordena a ascendente y b descendente. Si quieres las dos descendentes: ORDER BY a DESC, b DESC.
• Ordenar por ordinal en código de producción. ORDER BY 2 se rompe en silencio cuando alguien reordena el SELECT.
• Suponer que los nulos van donde tú crees. PostgreSQL los pone al final en ASC; MySQL, al principio. Si importa, escribe NULLS FIRST o NULLS LAST.
• Ordenar fechas guardadas como texto. Orden alfabético, no cronológico. Usa el tipo DATE.
• Ordenar números guardados como texto. '10' < '9' es verdadero alfabéticamente. Mismo problema.
• Sorprenderse del sitio de la Ñ o de los acentos. Depende de la colación de la base. Compruébala con SHOW lc_collate;.
• Consejo: usa COLLATE solo cuando de verdad haga falta, porque impide aprovechar los índices.
• Consejo: para informes, ordena por lo que el lector busca. Un listado de productos se ordena por nombre si van a buscar uno concreto, y por precio si van a comparar.
• Consejo: comprueba el orden con los extremos. Mira la primera y la última fila: suelen delatar de inmediato un ASC/DESC invertido o unos nulos mal colocados.

Dirección quiere el listado del catálogo tal como aparecerá en la web: solo productos activos, agrupados por categoría de menor a mayor, y dentro de cada categoría por nombre alfabético. Muestra categoria_id, id, nombre y precio. Explica por qué el resultado tiene 19 filas.

Recursos humanos pide la plantilla ordenada por salario de mayor a menor, mostrando nombre, apellidos, puesto, salario y jefe_id. Añade lo necesario para que el orden sea determinista y responde: ¿dónde aparece Rosa Alcázar Vives y por qué su jefe_id nulo no afecta al orden?

Sobre lineas_pedido, obtén las líneas ordenadas por importe de mayor a menor, mostrando id, pedido_id, producto_id, cantidad y el importe redondeado a dos decimales. Escribe la consulta de dos formas —usando el alias y repitiendo la expresión— y explica por qué ambas funcionan aquí pero solo una de ellas funcionaría en un WHERE.

Solución 1

19 filas porque WHERE activo descarta el producto 20 (Cápsulas de espirulina), el único descatalogado. Y por eso la categoría 6 no aparece en el listado: era su único producto.

Dos observaciones sobre el orden:

• Dentro de cada categoría, el id ya no es creciente: la categoría 2 empieza por el producto 8 porque "Aceite corporal" va antes alfabéticamente que "Bálsamo", "Champú" y "Crema". Es la prueba de que el orden lo manda el ORDER BY y no la tabla.
• En la categoría 2, "Champú sólido" va antes que "Crema facial" porque comparten la C inicial y desempata la segunda letra (h antes que r); las tildes de "Champú" y "Bálsamo" no intervienen aquí porque están más allá del punto en que ya se ha decidido el orden. Si algún producto empezara por una letra acentuada, en una base con colación C aparecería al final de todo el listado. Es el efecto de la sección 6.

Solución 2

Rosa Alcázar Vives aparece la primera, y no por su jefe_id nulo sino porque tiene el salario más alto (62 000 €). Su jefe_id nulo no influye en absoluto en el orden, porque esa columna no participa en el ORDER BY: aparece en el resultado pero no como criterio de ordenación. Es una distinción importante: los nulos solo alteran el orden de las columnas por las que ordenas.

El , id final garantiza el determinismo. En estos datos ningún salario se repite, así que no cambia nada hoy; el día que se contrate a dos comerciales con el mismo sueldo, el listado seguirá saliendo siempre igual.

Solución 3

Versión con alias:

Versión con la expresión repetida:

Ambas devuelven lo mismo. Las diez primeras filas de las 47:

Por qué ambas funcionan aquí. ORDER BY se ejecuta después de SELECT, así que en ese momento el alias importe ya existe y la expresión también puede reevaluarse. Las dos formas son válidas y PostgreSQL genera el mismo plan.

Por qué solo una funcionaría en WHERE. WHERE se ejecuta antes que SELECT, así que el alias todavía no ha nacido: WHERE importe > 30 daría column "importe" does not exist. Únicamente la versión con la expresión completa sirve para filtrar, como viste en 02-03. Toda esta lección se apoya en esa misma asimetría del orden lógico de ejecución.

Fíjate además en el empate de la fila 7 y 8: las líneas 9 y 42 valen exactamente 22.00 € (una unidad de matcha en ambos casos). El , id final es lo que decide que salga antes la 9. Sin él, el orden entre ambas sería impredecible, y un informe paginado podría llegar a mostrar la misma línea dos veces o ninguna.

Ya controlas el orden de tus resultados:

• Sin ORDER BY no hay orden garantizado. Un UPDATE, un índice o el paralelismo pueden cambiarlo sin previo aviso.
• ASC (por defecto) y DESC deciden el sentido, y cada columna del ORDER BY lleva el suyo.
• Ordenar por varias columnas resuelve los empates, y terminar con una columna única hace el orden determinista: imprescindible para paginar y para que las pruebas no fallen al azar.
• Puedes ordenar por alias, por expresión y por posición ordinal; esta última solo para explorar, porque se rompe en silencio.
• Los nulos van al final en ASC y al principio en DESC en PostgreSQL, justo al revés que en MySQL, SQLite y SQL Server. NULLS FIRST / NULLS LAST lo hace explícito.
• El orden del texto depende de la colación de la base: con C los acentos y la Ñ van al final de todo; con reglas lingüísticas o ICU, en su sitio. COLLATE "es-ES-x-icu" fuerza el criterio español.
• Las fechas ordenan cronológicamente si —y solo si— están guardadas en columnas de tipo fecha.
• ORDER BY es el paso 4 del orden lógico: ya existen los alias, aún se ven todas las columnas, y solo queda LIMIT por delante.

En la última lección del módulo, Limitando resultados con LIMIT, añadirás el paso 5 y cerrarás el ciclo completo de una consulta. Verás por qué LIMIT es lo primero que se escribe al explorar una tabla desconocida, cómo se construye una paginación con OFFSET, por qué esa paginación se degrada y se descuadra en tablas grandes, y qué se hace en su lugar en las aplicaciones que van en serio.