SQL: row_number over partition

Hoy voy a hacer una entrada sobre una consulta en Oracle que me parece muy interesante, utilizando la funcion row_number over partition.

Se utiliza para ordenar registros en grupos.

Lo más sencillo es que lo explique con un ejemplo: Necesitamos el primer curso publicado por un autor.

Nos ponemos en situación... un autor tiene una serie de cursos publicados, tenemos que hacer una consulta de forma que tengamos los cursos de cada autor, y sacar de alguna forma cual fue el primero que publicó.

Podríamos hacer la consulta completa de todos los autores y sus cursos, y por programa sacar el más reciente de cada uno. Pero esa consulta tiene un coste muy alto, y hay que añadirle además el coste de recorrer todo el resultset.

La mejor opción sería utilizar el row_number over partition:

row_number() over (order by col_1 [, col_2...])

row_number() over (partition by col_n [, col_m...] order by col_1 [, col_2...])

En el ejemplo la consulta sería:

select id_autor, nombre_autor, id_curso, titulo_curso
from (
__select cursos.id_autor, nombre_autor, id_curso,
____titulo_curso, fecha_publi
____row_number() over
______(partition by cursos.id_autor order by fecha_publi) ncurso
__from autores
__left join cursos
__on autores.id_autor=cursos.id_autor) cursos_autor
where ncurso=1;

Vemos que hacemos la join entre cursos y autores para relacionarlos, y en un campo asginamos un row_number particionado por el id_autor, y ordenado por la fecha_publicacion. La subconsulta con el row_number devuelve lo siguiente:

Vemos que el campo ncurso es un número correlativo por autor, y se asigna por el orden de la fecha_publicacion.

En la consulta completa vemos que hemos seleccionado solo los cursos que tienen ncurso igual a 1, es decir, el primero que son los que tienen la fecha más antigua.