Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
LEAD and LAG
FIRST_VALUE, LAST_VALUE, NTH_VALUE
21. NTH_VALUE(x, n)
Summary

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!