NTH_VALUE function in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NTH_VALUE function do in SQL?

It returns the value of the n-th row in an ordered set of rows within a window or partition.

beginner

How do you specify which row's value NTH_VALUE returns?

You provide the position n as the first argument, like NTH_VALUE(column, n), where n is the row number in the ordered window.

intermediate

What happens if the NTH_VALUE function's n is larger than the number of rows in the window?

The function returns NULL because there is no n-th row to return.

beginner

Why is the ORDER BY clause important when using NTH_VALUE?

Because NTH_VALUE returns the value from the n-th row in the ordered set, the order defines which row is considered first, second, etc.

advanced

Can NTH_VALUE be used without a window frame clause?

Yes, but by default the window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which may affect results. To get consistent results, specify a frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

What does NTH_VALUE(salary, 3) OVER (ORDER BY salary) return?

A The third lowest salary in the ordered set

B The third highest salary in the ordered set

C The salary of the current row

D The average of the first three salaries

The ORDER BY salary orders salaries ascending by default, so the 3rd row is the third lowest salary.

If the window has only 2 rows, what does NTH_VALUE(column, 5) return?

A The value of the 2nd row

B NULL

C The value of the 5th row

D An error

Since there is no 5th row, the function returns NULL.

Which clause is mandatory to use NTH_VALUE correctly?

A HAVING

B GROUP BY

C ORDER BY

D WHERE

ORDER BY defines the row order for NTH_VALUE to pick the n-th row.

What is the default window frame for NTH_VALUE if not specified?

A RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

B ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

C ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

D No frame is applied

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which can affect results.

Which SQL concept does NTH_VALUE belong to?

A Data definition language

B Scalar functions

C Aggregate functions

D Window functions

NTH_VALUE is a window function that operates over a set of rows.

Explain how the NTH_VALUE function works and why the ORDER BY clause is important.

Think about how you pick the n-th item from a sorted list.

Describe what happens if the n in NTH_VALUE(column, n) is larger than the number of rows in the window.

What if you ask for the 10th item but only 5 exist?