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.
NTH_VALUE(opened, 2) OVER(
ORDER BY opened
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
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
...OVER(ORDER BY opened DESC)...