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)...
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.
Order by the revenue in the DESCending order and remember to define the window frame!
