ROW_NUMBER, o cómo obtener la enésima fila de un conjunto de resultados

En ocasiones podemos querer obtener un resultado que sea un subconjunto de otro resultado, ordenado de una forma concreta.

Además de la cláusula TOP (que nos permite obtener un número o porcentaje concreto de filas sobre el total), SQL Server vio como se le añadían las funciones de ranking a partir de su versión de 2005.

Una posible aplicación de ellas serviría para resolver la siguiente cuestión: ¿Cómo devolver la enésima fila de un conjunto de resultados?


Evidentemente, si la fila a devolver fuese la primera o la última, la cláusula TOP nos serviría para tal propósito, pero, dado el siguiente conjunto de datos:

Nombre  Edad      Sexo
-------     -------- ------
Andrés     26          V
Eva          42          H
Javier       18          V
Manolo    54          V
María       32          H
Pedro       20          V
Vicente     24          V


¿Cómo podríamos obtener el nombre de la segunda persona con más edad de entre ellas?

ROW_NUMBER al rescate

Una de las funciones de ranking que nos ofrece SQL Server es ROW_NUMBER que nos facilita enormemente esta tarea.

Como podemos leer en la MSDN, la función devuelve el número secuencial de una fila de una partición de un conjunto de resultados, comenzando con 1 para la primera fila de cada partición.

Nosotros decidimos la ordenación y, en caso en que fuese necesario, las posibles particiones de la secuencia numérica. Pondremos un ejemplo con particiones a continuación.

Para responder a nuestra pregunta bastaría con hacer cualquier de estas dos consultas:

Mediante subconsulta:


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Edad) AS Orden, Nombre, Edad
FROM Gente
) T1
WHERE Orden = 2

Mediante CTE:


;WITH T1 AS
(
SELECT ROW_NUMBER() OVER (ORDER BY Edad) AS Orden, Nombre, Edad
FROM Gente
)
SELECT *
FROM T1
WHERE Orden = 2

Que nos devolverían el siguiente resultado:

Orden           Nombre  Edad
------------- -------- -----------
2                    Pedro     20

Usando particiones


Imaginemos ahora que reformulamos la pregunta inicial de la siguiente manera:

¿Cómo podríamos obtener el nombre de la segunda persona con más edad de cada sexo?

Una partición indica un cambio de valor en una columna que hace que la secuencia de números vuelva a empezar por 1.

En este caso queremos saber la fila que ocupa la posición 2 para varones (aquéllos cuya columna sexo tiene el valor V) y para hembras (con la letra H). Por ello, necesitaremos particionar por sexo, de tal manera que ROW_NUMBER inicie la cuenta en 1 para cada grupo de resultados, sin necesidad de hacer dos consultas distintas en las que cambiaríamos la condición del filtro modificando en cada caso la cláusula WHERE.

Quedaría así:

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Sexo ORDER BY Edad) AS Orden, Nombre, Edad, Sexo
FROM Gente
) T1
WHERE Orden = 2

Y el resultado, la respuesta a nuestra pregunta:

Orden    Nombre  Edad      Sexo
-------- ---------  -------- -------
2            Eva           42         H
2            Pedro       20         V


Comentarios

  1. Me alegro de que te haya sido útil.
    Cualquier duda que tengáis la podéis plantear para que la intentemos resolver y explicar en el blog 🙋
    Saludos

    ResponderEliminar
  2. Esto aplica para hacer un TOP 5 por cada mes/año?

    ResponderEliminar
  3. Hola, Carlos:

    Sí, se puede usar perfectamente para ese propósito, haciendo uso de la cláusula PARTITION BY. Suponiendo que tengas una tabla con los campos "Número de pedido", "FechaCreacion", "Importe" y quisieras ordenar los pedidos por año y mes, la consulta sería algo así:

    SELECT ROW_NUMBER() OVER (PARTITION BY DATEPART(YEAR, FechaCreacion), DATEPART(MONTH, FechaCreacion) ORDER BY Importe DESC) AS Ordenacion, NumeroPedido, FechaCreacion, Importe
    FROM Schema.Tabla.

    Una vez ordenados, podrías meter esa consulta dentro de otra, así:

    SELECT *
    FROM
    (
    -- Consulta anterior aquí
    ) T
    WHERE T.Ordenacion <= 5

    Espero haberte ayudado.

    Saludos,

    Jaime

    ResponderEliminar
  4. Buenas,
    Ha pasado mucho tiempo desde el ultimo comentario, pero aqui dejo mi pequeño
    grano.

    ¿Cómo podríamos obtener el nombre de la segunda persona con más edad de entre ellas?

    Una forma sencilla de acceder al elemento segundo elemento de una consulta es realizando lo siguiente.

    select top 2 @nombre=nombre from tabla_nombre
    order by edad desc

    Saludos
    _Willa

    ResponderEliminar
    Respuestas
    1. Hola _Willa:

      Esa consulta que propones puede funcionar en algunos casos aislados, pero no siempre. Por ejemplo, si quieres recuperar algún dato asociado a esa persona (su edad, dirección, etc) no podrías hacerlo en la misma consulta, puesto que en una SELECT no se pueden combinar asignaciones a variables y consulta de otros campos. SQL te daría el error:

      Msg 141, Level 15, State 1, Line 13
      Una instrucción SELECT que asigna un valor a una variable no debe combinarse con operaciones de recuperación de datos.

      Por otro lado, imagina que tienes hombres y mujeres en esa tabla: si quisieras obtener en una consulta el segundo con más edad de entre los hombres y la segunda con más edad de entre las mujeres, tampoco podrías. Necesitarías dos consultas con cláusulas WHERE distintas. Con ROW_NUMBER podrías hacer uso de la cláusula PARTITION BY para obtener los dos resultados en la misma consulta.

      Saludos y gracias por tu comentario.

      Eliminar
    2. Del primer bloque que comentas se podría cruzar con otra tabla a través de un INNER JOIN, para obtener otra información de tablas secundarias.

      SELECT TOP 2 @DATO1=aa.nombre @direcc=bb.dirr ... from tbl_nombre aa
      inner join tbl_dire bb on aa.id=bb.id
      order by aa.edad desc

      Con respecto al segundo bloque que comentas, no se me ocurre otra cosa que hacer lo que indicas, o en dos consultas.

      Enhorabuena por esta bloq.

      Eliminar
    3. Gracias Willa 😄

      Creo que no me he explicado bien. No me refería a recoger un dato desde otra tabla, me refería a que no puedes combinar en una consulta SELECT una asignación y una consulta. Por ejemplo. No puedes hacer esto:

      DECLARE @nombre nvarchar(5);
      SELECT TOP 2 @nombre = Nombre, Edad
      FROM dbo.Personas
      ORDER BY Edad DESC

      Esta consulta fallaría, dando el error que he indicado anteriormenye al intentar asignar el campo nombre a la variable @nombre y pretender consultar la edad también. Tendrías que hacer dos consultas.

      Saludos!

      Eliminar
  5. Gracias por tu tiempo amigo, me salvaste la vida y el trabajo :v.

    ResponderEliminar
    Respuestas
    1. Me alegro de haberte ayudado, Jorge.
      Cualquier duda que tengáis la podéis plantear aquí.

      Saludos

      Eliminar
  6. Yeah. Un dia de trabajo, resuelto en una linea. Gracias

    ResponderEliminar
  7. excelente. saludos y muchas gracias por compartir.

    ResponderEliminar
  8. Buen post, tengo un consulta, se puede combinar el where ,distinct.
    Por ejemplo si tengo la tabla departamentos con los colummnas numero,nombre,ubicacion y quisiera mostar el numero pero donde la ubicacion sea distinta.

    ResponderEliminar
    Respuestas
    1. Hola, Gabriel:

      Para filtrar resultados distintos de un valor en una columna debes usar la siguiente sintaxis:

      WHERE Columna <> Valor

      En tu caso sería:

      WHERE Ubicacion <> N'valor que quieres que no se seleccione'

      Verás que he puesto N''. La N es necesaria si usas datos Unicode (nchar o nvarchar). Si no es así (char o varchar) debes quitar la N.

      Saludos.

      Eliminar
  9. Excelente!!! Muchas gracias por la explicación me he ahorrado horas de trabajo, ya que de lo contrario debía obtener el TOP 20 de cada días de un total de 6 meses de información transaccional de forma manual.

    Saludos,

    ResponderEliminar
  10. Hola, Suponiendo que tengo una tabla que tiene los campos factura_id, ruta_imagen, tipodocumento y cada factura_id tiene mas de 20 registros con esos campos, pero deseo hacer top 5 de cada factura_id de 30 factura_id especificos...

    ResponderEliminar
    Respuestas
    1. Hola, Fernando:

      Lo que necesitas se puede conseguir cambiando ligeramente el ejemplo del blog:

      SELECT *
      FROM
      (
      SELECT ROW_NUMBER() OVER (PARTITION BY factura_id ORDER BY CampoParaOrdenar) AS Orden, CamposDeLaQuery
      FROM TablaFacturas
      ) T1
      WHERE Orden <= 5


      Saludos

      Eliminar
    2. Hola Jaime, gracias por atender nuestras preguntas, lo que quería hacer entender es que si puedo colocar ejemplo:

      un Where factura_id in ('1234','5678','91011') y que me traiga un top 5 de esos 3

      Eliminar
    3. Este comentario ha sido eliminado por el autor.

      Eliminar
  11. Hola, ya entendí como funciona en cuanto a lo que quería era realizar top 5 de facturas especificas, entonces quedaría de la siguiente forma:

    SELECT *
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY factura_id ORDER BY CampoParaOrdenar) AS Orden, CamposDeLaQuery
    FROM TablaFacturas
    Where factura_id in ('1234','5678','91011')-- con esto especifico
    --que me traiga un top 5 de cada factua
    ) T1
    WHERE Orden <= 5

    Gracias, lo entendi un poco tarde, soy estudiante.

    ResponderEliminar
  12. Excelente! he optimizado una consulta gracias a tu post te lo agradezco.

    ResponderEliminar
  13. Buenas a todos, Tengo la siguiente situación:
    En mi base de datos hay una tabla llamada Productos, la cual tiene los campos: CodigProdu(nvarchar), Nombre(nvarchar), etc.

    En ocasiones El Codigo del producto(CodigProdu) se guardará alfanumérico, pero en otras se guardará como numérico.

    El problema surge cuando llamo a los datos de la tabla y le digo que los ordene por el Código del Producto. Necesito que me muestre los códigos en orden numérico, pero los ordena mal. Por ej: 1,11,12,2,22,3, y así por el estilo.

    ¿Como podría hacer para que mi consulta muestre los datos ordenados numéricamente por el Código de Producto, sin cambiar el tipo de datos Nvarchar?. Muchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola Jorge:

      La verdad es que esas situaciones son poco deseables. En muchas ocasiones se suelen poner ceros de relleno de lante del número, así 01, 02... van delante de 11, 12...

      Personalmente no me acaba de convencer esa solución, porque te puede fallar después alguna comparación que hagas entre 01 y 1, por ejemplo.

      La alternativa que se me ocurre es separar la consulta en dos y unir los resultados mediante UNIÓN ALL.

      En la primera obtendrías los códigos numéricos y en la segunda los alfanuméricos, haciendo uso de la función ISNUMERIC.
      https://msdn.microsoft.com/es-es/library/ms186272.aspx

      SELECT *, CAST(CodigProdu AS int) AS CodigoNumerico
      FROM Productos
      WHERE ISNUMERIC(CodigProdu) = 1
      ORDER BY CodigoNumerico

      Union ALL

      SELECT *
      FROM Productos
      WHERE ISNUMERIC(CodigProd) <> 1
      ORDER BY CodigProd

      Habría que comprobar el rendimiento de la consulta.

      Saludos

      Eliminar
    2. Ok, muchas gracias por tu tiempo.... Lo probaré . Gracias 👍

      Eliminar
  14. Buenos días, yo tengo otra situación:

    Tengo una tabla atenciones, en ella tengo el dato del NroDocumento del medico y una fecha de atención y hago un row_number() para contar la cantidad de atenciones que tiene un medico por día, el tema esta en que necesito que esta numeración tenga un tope de 25 registros y que cuando llegue a ese numero reinicie la numeración, además, cada 25 registros tengo que agruparlos a un numero de pagina, para esto hago un dense_rak() con los mismos parametros que tiene el row_number(), también tengo un tope en para las paginas que son 20 y debe reiniciarse igual que los Reg. es algo así:

    Pag Reg NroDoc FechaAtencion
    ---- ---- ------- ----------
    1 1 7445566 2016-11-04
    1 2 7445566 2016-11-04
    1 3 7445566 2016-11-04
    .
    .
    .
    1 24 7445566 2016-11-04
    1 25 7445566 2016-11-04
    2 1 7445566 2016-11-04
    2 2 7445566 2016-11-04
    2 3 7445566 2016-11-04
    .
    .
    .
    2 24 7445566 2016-11-04
    2 25 7445566 2016-11-04

    Ahora ando liado con eso, seria una gran ayuda si me das una salida para ello. Saludos

    ResponderEliminar

Publicar un comentario