FIRST_VALUE and LAST_VALUE in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL function FIRST_VALUE() do?

It returns the first value in an ordered set of rows within a window or partition.

beginner

What is the purpose of LAST_VALUE() in SQL?

It returns the last value in an ordered set of rows within a window or partition.

intermediate

How do FIRST_VALUE() and LAST_VALUE() differ from aggregate functions like MIN() and MAX()?

FIRST_VALUE() and LAST_VALUE() return values based on row order within a window, while MIN() and MAX() return the smallest or largest value regardless of order.

beginner

In SQL, what clause is required to use FIRST_VALUE() and LAST_VALUE() properly?

The OVER() clause with an ORDER BY to define the window or partition and the order of rows.

advanced

Why might LAST_VALUE() return unexpected results without proper framing?

Because by default the window frame ends at the current row, LAST_VALUE() may return the current row's value instead of the last in the partition unless frame boundaries are set.

What does FIRST_VALUE(column) OVER (ORDER BY date) return?

A The earliest value of column based on date order

B The smallest value of column overall

C The last value of column in the table

D The average value of column

FIRST_VALUE returns the first value in the order specified, here by date.

Which clause is mandatory to use with LAST_VALUE()?

A HAVING

B GROUP BY

C ORDER BY inside OVER()

D WHERE

LAST_VALUE requires an ORDER BY clause inside the OVER() to define row order.

What happens if you use LAST_VALUE() without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING?

A It returns the first value

B It returns NULL

C It returns the last value in the entire partition

D It returns the value of the current row

Without proper framing, LAST_VALUE returns the value of the current row because the default frame ends at the current row.

Which function would you use to get the first value in each group of rows?

A FIRST_VALUE()

B MAX()

C MIN()

D COUNT()

FIRST_VALUE() returns the first value in an ordered window or partition.

Can FIRST_VALUE() and LAST_VALUE() be used without window functions?

A Yes, they work like aggregate functions

B No, they require OVER() clause

C Yes, but only in WHERE clause

D No, they are not SQL functions

They require the OVER() clause to define the window for their operation.

Explain how FIRST_VALUE() and LAST_VALUE() work in SQL and when you would use them.

Think about how you find the first or last item in a sorted list.

Describe the importance of window framing when using LAST_VALUE() and how to fix common issues.

Consider how the window frame limits which rows LAST_VALUE() sees.