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

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,
  opened,
  LAST_VALUE(opened) OVER(ORDER BY opened ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM website;

Exercise

Alright, let's run the new template to see if it works.

Console

Code editor

Result

TableConsole