Introduction
LEAD and LAG
FIRST_VALUE, LAST_VALUE, NTH_VALUE
15. FIRST_VALUE(x)
Revision

Instruction

Good job! LEAD and LAG are 2 functions which are always relative to the current row. Now, we'll get to know three other functions that are independent of the current row.

The first one is FIRST_VALUE(x). As you may guess, it returns the first value in the column x in the given order. Take a look:

SELECT
  name,
  opened,
  budget,
  FIRST_VALUE(budget) OVER(ORDER BY opened)
FROM website;

Here, we still sort rows by the opening date (ORDER BY opened), but we show the lowest budget instead of the first opening date (FIRST_VALUE(budget)). In this way, we can show the budget for the website that was opened first.

Note that this would be impossible to achieve with a simple MIN(...) function. MIN(budget) would simply show the smallest budget: 500 in this case. That is not the same as the budget of the first website (3000).

Exercise

Show the statistics for website_id = 2. For each row, show the day, the number of users and the smallest number of users ever.

Stuck? Here's a hint!

Use FIRST_VALUE(users) and sort by the number of users.

Console

Code editor

Result

TableConsole