SELECT DISTINCT ON (expr) ... devuelve una fila por cada valor distinto de expr: la primera fila de ese grupo según el ORDER BY. Ordena primero por la clave del grupo y después por lo que decide cuál es la "primera". Para el último pedido de cada cliente, eso es ORDER BY customer, created_at DESC.
Devuelve tres filas: el pedido 2 para asha, el 4 para ben y el 5 para chen, el más reciente de cada cliente. DISTINCT ON es una extensión de PostgreSQL. Otras bases de datos necesitan la forma con funciones de ventana que verás más abajo.
Cómo decide el ORDER BY qué fila se conserva
PostgreSQL ordena las filas y se queda con la primera cada vez que cambia el valor de DISTINCT ON. Por eso:
- El
ORDER BYdebe empezar con las expresiones deDISTINCT ON, o PostgreSQL lanza "SELECT DISTINCT ON expressions must match initial ORDER BY expressions". - Sin un
ORDER BYque vaya más allá de esas expresiones, la fila que se conserva es impredecible y puede cambiar entre ejecuciones.
Cambia created_at DESC por total DESC y la misma consulta devuelve el pedido más grande de cada cliente:
Ahora la fila de asha es el pedido 1 (40) y la de ben el pedido 3 (60).
Resolver los empates a propósito
Cuando dos filas empatan en la columna de orden, cualquiera de las dos puede ganar. Añade una última clave de orden que sea única, como la clave primaria, para que el resultado sea siempre el mismo:
Las filas 2 y 3 tienen 95 puntos. id como última clave hace que gane la fila 2.
Agrupar por más de una columna
Pon varias expresiones para obtener una fila por combinación:
Cada par de sensor y tipo conserva su lectura más reciente: s1 humidity 40, s1 temp 22, s2 temp 19.
La versión portable con ROW_NUMBER
ROW_NUMBER() numera las filas dentro de cada partición. Quedarte con el número 1 da el mismo resultado que DISTINCT ON, y también funciona en MySQL 8, SQL Server y SQLite:
SELECT customer, id, total, created_at
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Usa esta forma cuando necesites las n primeras por grupo (rn <= 3) o cuando la consulta tenga que correr en otra base de datos. La página de funciones de ventana explica PARTITION BY.
Hacerlo rápido
DISTINCT ON ordena la entrada, así que en una tabla grande dale un índice que coincida con el ORDER BY:
CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);
Así PostgreSQL puede leer las filas ya en orden en lugar de ordenarlas. Con pocos grupos y muchas filas por grupo, un join LATERAL que toma LIMIT 1 por cliente desde el mismo índice suele leer menos filas. La página de joins muestra LATERAL. Compara las dos opciones con EXPLAIN ANALYZE sobre tus datos.
Preguntas frecuentes
¿Qué diferencia hay entre DISTINCT y DISTINCT ON?
DISTINCT elimina las filas que son idénticas en todas las columnas seleccionadas. DISTINCT ON (a) deja una fila por cada valor de a, y las demás columnas salen de la fila que quede primera al ordenar ese grupo.
¿DISTINCT ON es SQL estándar?
No. Es una extensión de PostgreSQL, así que no funciona en MySQL ni en SQL Server. La versión portable es ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) en una subconsulta, filtrando el número de fila 1.
¿Por qué me sale "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?
El ORDER BY tiene que empezar con las mismas expresiones que DISTINCT ON, en el mismo orden. Escribe DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, no solo ORDER BY created_at DESC.
¿DISTINCT ON puede devolver las 3 primeras filas por grupo?
No, devuelve exactamente una fila por grupo. Para las n primeras, numera las filas con ROW_NUMBER() y quédate con rn <= 3, o usa una subconsulta LATERAL con LIMIT 3.