Introduction
LEAD() and LAG()
FIRST_VALUE(), LAST_VALUE()
18. LAST_VALUE(x) with window frame
Summary

Instruction

Well... The query didn't work! Instead of the latest opening date, we saw the current opening date.

In order to understand why this happened, we need to refer to the previous part of our course, where we talked about default window frames:

If there is an ORDER BY clause, RANGE UNBOUNDED PRECEDING will be used as the default window frame.

And this is precisely the cause of our troubles. We indeed used ORDER BY within OVER(...), which is why LAST_VALUE(x) only considers the rows from the first row until the current row. The solution is quite simple: we need to define the right window frame:

SELECT
  Name,
  OpenDate,
  LAST_VALUE(OpenDate) OVER(ORDER BY OpenDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastOpenDate
FROM Website;

Exercise

Okay, let's run the new template and see if it works.