Introduction
LEAD and LAG
FIRST_VALUE, LAST_VALUE, NTH_VALUE
21. NTH_VALUE(x,n)
Revision

Instruction

Great! The last function we'll learn in this part is: NTH_VALUE(x,n). This function returns the value in the column x of the nth row in the given order.

SELECT
  name,
  opened,
  NTH_VALUE(opened,2) OVER(ORDER BY opened
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM website;

This time, we're showing the opening date of the current row together with the second row when sorted by the opening date. With NTH_VALUE, we also need to redefine the window frame. Otherwise, some rows will display incorrect values.

Just to remind you, you can always revert the order by adding the word DESC, which sometimes comes in handy with NTH_VALUE:

...OVER(ORDER BY opened DESC)...

Exercise

Take the statistics for the website with id = 2 between May 15 and May 31, 2016. Show the day, the revenue on that day and the third highest revenue in that period.

Stuck? Here's a hint!

Order by the revenue in the DESCending order and remember to define the window frame!

Console

Code editor

Result

TableConsole