Lección 12.6 · Tiempo de lectura: ~7 min

El top N por grupo (en inglés "greatest N per group") consiste en elegir las mejores filas de cada grupo: los tres mayores clientes de cada tienda, las dos películas más largas de cada categoría, el último pedido de cada cliente. Aparece constantemente en los informes y en las entrevistas de SQL. En esta lección lo resolverás con ROW_NUMBER() y PARTITION BY, verás cómo los empates cambian la respuesta y conocerás dos alternativas: una subconsulta correlacionada y una unión LATERAL.

Top N filas por grupo en SQL

ORDER BY ... LIMIT 3 devuelve las tres primeras filas del resultado completo, no de cada grupo. Agrupar tampoco ayuda: GROUP BY reduce cada grupo a una sola fila, y necesitamos varias filas originales por grupo. La solución es numerar las filas dentro de cada grupo y conservar las N primeras.

Los ejemplos se ejecutan en la base de datos de ejemplo Sakila (MySQL 8.0).

El ejercicio

Encuentra los tres clientes que más pagaron en cada una de las dos tiendas.

Primero, el total de cada cliente:

SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
       SUM(p.amount) AS total_paid
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
ORDER BY total_paid DESC
LIMIT 3;

Resultado: KARL SEAL (tienda 2, 221.55), ELEANOR HUNT (tienda 1, 216.54), CLARA SHAW (tienda 1, 195.58): los tres primeros en total, no por tienda.

Solución 1: ROW_NUMBER con PARTITION BY

PARTITION BY store_id reinicia la numeración en cada tienda; ORDER BY total_paid DESC le da el número 1 al cliente que más pagó. Una función de ventana no se puede usar en el WHERE de la misma consulta, así que el filtro va en la consulta externa:

WITH customer_total AS (
    SELECT c.store_id, c.customer_id, c.first_name, c.last_name,
           SUM(p.amount) AS total_paid
    FROM customer c
    JOIN payment p ON p.customer_id = c.customer_id
    GROUP BY c.store_id, c.customer_id, c.first_name, c.last_name
),
ranked AS (
    SELECT store_id, first_name, last_name, total_paid,
           ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_paid DESC) AS rn
    FROM customer_total
)
SELECT store_id, first_name, last_name, total_paid, rn
FROM ranked
WHERE rn <= 3
ORDER BY store_id, rn;

Resultado: 6 filas, tres por tienda.

Empates: ROW_NUMBER, RANK o DENSE_RANK

RHONDA KENNEDY y MARION SNYDER pagaron exactamente lo mismo, pero ROW_NUMBER les dio números distintos. Con empates, las tres funciones responden preguntas diferentes. Veamos las películas más largas de dos categorías, donde las duraciones se repiten a menudo:

WITH ranked AS (
    SELECT c.name AS category, f.title, f.length,
           ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS row_num,
           RANK()       OVER (PARTITION BY c.name ORDER BY f.length DESC) AS rnk,
           DENSE_RANK() OVER (PARTITION BY c.name ORDER BY f.length DESC) AS dense_rnk
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
)
SELECT category, title, length, row_num, rnk, dense_rnk
FROM ranked
WHERE category IN ('Action', 'Comedy') AND dense_rnk <= 3
ORDER BY category, row_num;

Resultado: 9 filas; en Action, dos películas comparten la duración máxima de 185 minutos.

• ROW_NUMBER() <= N: exactamente N filas por grupo; entre valores iguales, la elección es arbitraria. Agrega un criterio de desempate al ORDER BY, por ejemplo f.length DESC, f.title, para que el resultado sea estable.
• RANK() <= N: todos los que entrarían en el top N, así que un grupo puede devolver más de N filas.
• DENSE_RANK() <= N: las filas con los N mayores valores distintos.

En una entrevista, pregunta qué se pide: "las 3 mejores películas" suele ser ROW_NUMBER, "las películas con las 3 duraciones más largas" es DENSE_RANK.

Solución 2: subconsulta correlacionada

Sin funciones de ventana (MySQL antes de 8.0), una fila está en el top N de su grupo si menos de N filas del mismo grupo tienen un valor mayor:

SELECT c.name AS category, f.title, f.length
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
WHERE c.name = 'Action'
  AND (
      SELECT COUNT(*)
      FROM film f2
      JOIN film_category fc2 ON fc2.film_id = f2.film_id
      WHERE fc2.category_id = fc.category_id
        AND f2.length > f.length
  ) < 2
ORDER BY f.length DESC;

Resultado: 2 filas, DARN FORRESTER y WORST BANGER, las dos películas sin ninguna más larga en Action; se comporta como RANK() <= 2.

La subconsulta se ejecuta para cada fila, así que es más lenta en tablas grandes, pero funciona en cualquier base de datos.

Solución 3: unión LATERAL

Una subconsulta LATERAL puede hacer referencia a las columnas de las tablas que aparecen antes en FROM. Eso permite escribir literalmente "para cada categoría, toma sus 2 películas más largas", con ORDER BY y LIMIT dentro:

SELECT c.name AS category, top_films.title, top_films.length
FROM category c
JOIN LATERAL (
    SELECT f.title, f.length
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    WHERE fc.category_id = c.category_id
    ORDER BY f.length DESC, f.title
    LIMIT 2
) AS top_films ON TRUE
WHERE c.name IN ('Action', 'Comedy')
ORDER BY category, top_films.length DESC;

Resultado: 4 filas: DARN FORRESTER y WORST BANGER para Action, CONTROL ANTHEM y SATURN NAME para Comedy.

LATERAL lo admiten PostgreSQL y MySQL 8.0.14+, pero no MariaDB; SQL Server usa CROSS APPLY para la misma idea. Con un índice sobre las columnas de agrupación y orden, suele ser la forma más rápida cuando los grupos son grandes y N es pequeño.

Práctica

• Zonas de recogida principales por mes
• Empleados mejor pagados por departamento
• Ranking de películas por popularidad

Conclusiones clave:

• LIMIT y GROUP BY no pueden devolver varias filas por grupo; en su lugar, numera las filas dentro de cada grupo.
• ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) con WHERE rn <= N en una consulta externa es la solución estándar.
• Con empates, ROW_NUMBER devuelve exactamente N filas, RANK puede devolver más y DENSE_RANK devuelve los N mayores valores distintos.
• Una subconsulta correlacionada resuelve el ejercicio sin funciones de ventana, pero es más lenta.
• JOIN LATERAL (o CROSS APPLY en SQL Server) con ORDER BY ... LIMIT N es una alternativa legible y muchas veces rápida.

Preguntas frecuentes

¿Cómo selecciono las N primeras filas de cada grupo en SQL?

Numera las filas de cada grupo con ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) en una CTE o subconsulta y luego, en la consulta externa, conserva las filas con un número hasta N.

¿Por qué no puedo usar la función de ventana en WHERE?

Las funciones de ventana se calculan después de WHERE, GROUP BY y HAVING, durante el SELECT. Para filtrar por su resultado, calcúlalas en una CTE o subconsulta y filtra en la consulta externa. Algunas bases de datos (Snowflake, DuckDB, BigQuery) tienen para esto la cláusula QUALIFY.

¿Cuál es la diferencia entre ROW_NUMBER, RANK y DENSE_RANK para el top N?

ROW_NUMBER da a cada fila un número único, así que devuelve exactamente N filas y resuelve los empates de forma arbitraria. RANK da el mismo puesto a valores iguales y salta los números siguientes, por lo que puede devolver más de N filas. DENSE_RANK no salta números y devuelve todas las filas con los N mayores valores distintos.

¿Cómo obtengo la última fila de cada cliente?

Es un top 1 por grupo: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) y WHERE rn = 1. En PostgreSQL, SELECT DISTINCT ON (customer_id) ... ORDER BY customer_id, created_at DESC es una alternativa más corta.