SQLearnAcademy
←Referencia de funciones
De ventanaestándar SQL

NTH_VALUE

Toma el valor de la fila N de la ventana: la segunda, la tercera…

Sintaxis

NTH_VALUE(<expresión>, <n>) OVER (
  ORDER BY <criterio de orden>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE(<expresión>, <n>) OVER (
  PARTITION BY <grupo> ORDER BY <criterio de orden>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Como FIRST_VALUE, pero para cualquier número: NTH_VALUE(salary, 2) es el segundo salario de la ventana. El número de filas no cambia y cada una recibe el valor de su propia ventana. Si en la ventana todavía no hay fila N, el resultado es NULL.

Datos para los ejemplos

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

Estas tablas ya están creadas en el entorno de pruebas y los ejemplos de abajo las usan. ¿Quieres repetirlo en tu propia base de datos? Copia el SQL.

El segundo salario junto a cada uno
SELECT name, salary,
NTH_VALUE(salary, 2) OVER (
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_salary
FROM employees;
namesalarysecond_salary
Olivia13501200
Emma12001200
Sophia12001200
Liam10001200
Noah9501200
Sin marco: NULL al principio
SELECT name, salary,
NTH_VALUE(name, 2) OVER (ORDER BY salary DESC) AS second_so_far
FROM employees;
namesalarysecond_so_far
Olivia1350NULL
Emma1200Emma
Sophia1200Emma
Liam1000Emma
Noah950Emma

Sin marco, la ventana termina en la fila actual (junto con sus empates). Para Olivia, la ventana solo la contiene a ella, así que todavía no hay segunda fila.

El segundo nombre de cada departamentoAvanzado
SELECT name, department, salary,
NTH_VALUE(name, 2) OVER (
PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_in_department
FROM employees;
namedepartmentsalarysecond_in_department
Emmabackend1200Liam
Liambackend1000Liam
Sophiadesign1200NULL
Oliviafrontend1350Noah
Noahfrontend950Noah

design tiene una sola persona, así que no hay segunda: NULL.

Ten en cuenta

Sin el marco ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, las primeras filas reciben NULL: la ventana termina en la fila actual y todavía no contiene la fila N.

El segundo salario más alto no siempre es NTH_VALUE(salary, 2): si los dos más altos son iguales, la segunda fila da el mismo número que la primera. El segundo valor distinto lo encuentra DENSE_RANK.

El resultado no se puede usar en el WHERE de la misma consulta: calcúlalo primero en una subconsulta y filtra fuera.

Funciones relacionadas

FIRST_VALUEDENSE_RANKLEAD