ROW_NUMBER, RANK y DENSE_RANK: empates y Top N por grupo

ROW_NUMBER asigna una posición única a cada fila. RANK comparte el rango entre empates y deja saltos. DENSE_RANK comparte el rango sin saltos. La elección cambia el significado de “los dos mejores” cuando hay puntuaciones iguales.

Función	Posiciones para 95, 95, 80	Uso adecuado
ROW_NUMBER	1, 2, 3 con un desempate explícito	Hasta N filas por grupo
RANK	1, 1, 3	Clasificación de competición
DENSE_RANK	1, 1, 2	Los N niveles de puntuación más altos

PARTITION BY team reinicia la numeración en cada equipo. A diferencia de GROUP BY, las funciones de ventana conservan las filas individuales. Puedes mostrar la puntuación de una persona y su posición en el mismo resultado.

Pruébalo en SQLite

Abre SQL Lab, pega esta preparación y ejecútala una vez. Después ejecuta cada consulta por separado. Los nombres de tablas y columnas se mantienen en inglés para usar el mismo SQL en todos los idiomas. La interfaz del laboratorio y las lecciones enlazadas están en inglés.

CREATE TABLE blog_scores (
  id INTEGER PRIMARY KEY, team TEXT, name TEXT, score INTEGER
);
INSERT INTO blog_scores VALUES
  (1, 'A', 'Asha', 95), (2, 'A', 'Luis', 95), (3, 'A', 'Mei', 80),
  (4, 'B', 'Noor', 90), (5, 'B', 'Omar', 70), (6, 'B', 'Pia', 70);

1. Compara las tres funciones con empates

El ORDER BY de ROW_NUMBER incluye id para resolver los empates de manera estable. RANK y DENSE_RANK ordenan solo por score y conservan los empates. Añadir id a su ORDER BY de ventana rompería esa igualdad. El ORDER BY final controla la presentación, por separado del cálculo de las ventanas.

SELECT team, name, score,
  ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS row_num,
  RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_num
FROM blog_scores
ORDER BY team, score DESC, id;

Resultado esperado

team	name	score	row_num	rank_num	dense_num
A	Asha	95	1	1	1
A	Luis	95	2	1	1
A	Mei	80	3	3	2
B	Noor	90	1	1	1
B	Omar	70	2	2	2
B	Pia	70	3	2	2

2. Devuelve hasta dos personas por equipo

Usa ROW_NUMBER y filtra la posición en una consulta exterior. El equipo B conserva a Noor y Omar; Pia queda fuera por el desempate con id. Cada equipo tiene como máximo dos filas. SQLite no permite usar el resultado de ventana en WHERE al mismo nivel, por eso la CTE crea un resultado intermedio filtrable.

WITH ranked AS (
  SELECT team, name, score, id,
    ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE position <= 2
ORDER BY team, position;

Resultado esperado

team	name	score
A	Asha	95
A	Luis	95
B	Noor	90
B	Omar	70

3. Devuelve los dos niveles de puntuación más altos

DENSE_RANK conserva a todas las personas de los dos niveles superiores. Ambos equipos devuelven tres personas en estos datos. Sirve cuando se debe incluir a todos los empatados en niveles válidos, pero no impone un límite exacto de dos personas.

WITH ranked AS (
  SELECT team, name, score, id,
    DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS score_band
  FROM blog_scores
)
SELECT team, name, score
FROM ranked
WHERE score_band <= 2
ORDER BY team, score DESC, id;

Resultado esperado

team	name	score
A	Asha	95
A	Luis	95
A	Mei	80
B	Noor	90
B	Omar	70
B	Pia	70

Practica y comprueba tu respuesta

Sustituye DENSE_RANK por RANK en la última consulta y conserva score_band <= 2. El equipo A devuelve solo a Asha y Luis: Mei tiene rango 3 porque los dos líderes empatados ocupan las primeras dos posiciones. El equipo B sigue devolviendo a Noor, Omar y Pia.

Antes de elegir, aclara si se piden “dos personas”, “rangos de competición hasta dos” o “dos puntuaciones distintas”. Son reglas diferentes. Prueba datos con empates: las puntuaciones únicas no revelan la diferencia.

Añade un criterio de desempate único y estable a ROW_NUMBER para obtener selecciones reproducibles. Sin él, el motor puede cambiar el orden entre filas empatadas. Si puede haber puntuaciones NULL, decide si deben participar y define su orden o exclúyelas; el tratamiento del orden de NULL varía entre motores.

Para deduplicar, particiona por la clave duplicada y ordena por la fecha preferida más un ID único. Conserva la posición 1 en una consulta exterior, pero revisa primero las filas descartadas. Clasificar filas ayuda a seleccionarlas; no demuestra que eliminarlas sea seguro.

Documentación y próximos pasos

Practica con las lecciones de ranking y cláusula OVER. Las consultas funcionan en SQLite; PostgreSQL y MySQL 8 también admiten estas funciones. No se presupone una cláusula específica como QUALIFY.

• Documentación oficial de SQLite
• Documentación oficial del motor