Nth_VALUE: Grabbing That Specific 3rd Item

Welcome to **Day 72**. We've mastered `FIRST_VALUE` and `LAST_VALUE`. But what if you want the **second** visit or the **third** highest price?

The Nth_VALUE Function

This function allows you to specify exactly which row index شما want to retrieve.

SELECT

department,

employee_name,

salary,

NTH_VALUE(employee_name, 2) OVER(

PARTITION BY department

ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as second_highest_paid_in_dept

FROM employees;

Why this is better than OFFSET

Normally, to get the 2nd row, you would use `LIMIT 1 OFFSET 1`. But that only works for **one** result. `NTH_VALUE` allows you to calculate this for every single department in your company in one query!

When is it NULL?

If a department only has 1 employee, `NTH_VALUE(..., 2)` will return `NULL`. This is perfect because it's technically accurate!

Your Task for Today

Find the second-most recent order date for every customer.

*Day 73: Handling Ties in Window Functions—A Professional Guardrail.*