¿Seleccionar la primera fila de cada grupo GROUP BY?

lenguaje-todo: lang-sql -->

Como sugiere el título, me gustaría seleccionar la primera fila de cada conjunto de filas agrupadas con un GROUP BY.

En concreto, si tengo una tabla de compras con el siguiente aspecto

SELECT * FROM purchases;

Mi salida:

id | cliente | total
---+----------+------
 1 | Joe | 5
 2. Sally: 3
 3 | Joe | 2
 4 | Sally | 1

Me gustaría consultar el id de la mayor compra (total) realizada por cada cliente. Algo así:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

Salida esperada: pre> FIRST(id) | cliente | FIRST(total) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3

DineshDB
Pregunta editada 26º marzo 2018 в 8:18
Programación
sql postgresql sqlite group-by greatest-n-per-group
27º septiembre 2010 в 1:23
28 vistas

En PostgreSQL esto es típicamente más simple y más rápido (más optimización de rendimiento abajo):

SELECT DISTINCT ON (cliente)
       id, cliente, total
FROM compras
ORDER BY customer, total DESC, id;
O más corto (aunque no tan claro) con los números ordinales de las columnas de salida: 
SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;

Si total puede ser NULL (no hará daño de cualquier manera, pero querrá coincidir con los índices existentes):

...
ORDER BY customer, total DESC NULLS LAST, id;
###Puntos principales - **`DISTINCT ON`**][1] es una extensión de PostgreSQL del estándar (donde sólo se define `DISTINCT` en toda la lista `SELECT`). - Enumera cualquier número de expresiones en la cláusula `DISTINCT ON`, el valor de la fila combinada define los duplicados. [El manual:][2] > Obviamente, dos filas se consideran distintas si difieren en al menos > un valor de columna. **Los valores nulos se consideran iguales en esta comparación. El énfasis en negrita es mío. - La expresión `DISTINCT ON` puede combinarse con **`ORDER BY`**. Las expresiones principales tienen que coincidir con las expresiones principales de `DISTINCT ON` en el mismo orden. Puedes añadir expresiones *adicionales* a `ORDER BY` para elegir una fila concreta de cada grupo de pares. He añadido `id` como último elemento para romper los empates: *"Elige la fila con el menor `id` de cada grupo que comparta el mayor `total`"*. Para ordenar los resultados de una manera que no coincida con el orden que determina el primero por grupo, puede anidar la consulta anterior en una consulta externa con otro `ORDER BY`. Por ejemplo - https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104 - Si `total` puede ser NULL, lo más probable es que quieras la fila con el mayor valor no nulo. Añade **`NULLS LAST`** como se ha demostrado. Detalles: - https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492 - La lista `SELECT`** no está restringida por las expresiones en `DISTINCT ON` o `ORDER BY` de ninguna manera. (No es necesario en el caso simple de arriba): - No tiene que incluir ninguna de las expresiones en `DISTINCT ON` o `ORDER BY`. - Puede incluir cualquier otra expresión en la lista `SELECT`. Esto es fundamental para reemplazar consultas mucho más complejas con subconsultas y funciones de agregación/ventana. - He probado con las versiones 8.3 - 12 de Postgres. Pero la característica ha estado allí al menos desde la versión 7.1, así que básicamente siempre. ##Índice El índice *perfecto* para la consulta anterior sería un [índice multicolumna][3] que abarcara las tres columnas en la misma secuencia y con el mismo orden de clasificación: 
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);

Puede ser demasiado especializado. Pero utilícelo si el rendimiento de lectura para la consulta particular es crucial. Si tiene DESC NULLS LAST en la consulta, utilice lo mismo en el índice para que el orden de clasificación coincida y el índice sea aplicable.

Eficacia / Optimización del rendimiento

Sopese el coste y el beneficio antes de crear índices a medida para cada consulta. El potencial del índice anterior depende en gran medida de la distribución de los datos. El índice se utiliza porque proporciona datos preordenados. En Postgres 9.2 o posterior, la consulta también puede beneficiarse de un escaneo de sólo índice si el índice es más pequeño que la tabla subyacente. Sin embargo, el índice tiene que ser escaneado en su totalidad.

Erwin Brandstetter
Respuesta editada 28º agosto 2019 в 7:29
Comentarios (7)
Solución

En Oracle 9.2+ (no 8i+ como se dijo originalmente), SQL Server 2005+, PostgreSQL 8.4+, DB2, Firebird 3.0+, Teradata, Sybase, Vertica:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1

Soportado por cualquier base de datos:

Pero hay que añadir la lógica para romper los empates:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total
mach128x
Respuesta editada 7º diciembre 2017 в 6:42
Comentarios (7)

La solución no es muy eficiente como señala Erwin, debido a la presencia de SubQs

select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;
user2407394
Respuesta editada 17º junio 2013 в 8:39
Comentarios (4)