NTH_VALUE()

NTH_VALUE() is a SQL function in the Window Functions category. Returns the value from the Nth row in the window frame. The syntax is NTH_VALUE(column, n) OVER (PARTITION BY col ORDER BY col). It accepts column, n. It returns nth value in frame. A typical example: SELECT name, salary, NTH_VALUE(name, 3) OVER (ORDER BY salary DESC) AS third_highest FROM employees; -- Third highest paid employee name shown on each row A close relative is ROW_NUMBER(), which assigns a unique sequential integer to each row within its partition (1, 2, 3...). A close relative is RANK(), which similar to ROW_NUMBER but gives the same rank to equal values. Leaves gaps in ranking. A close relative is DENSE_RANK(), which like RANK but without gaps. Equal values get the same rank, and the next number follows sequentially. More about this category: Window/analytical functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE.