SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics/Get the second value in each partition
Pull a specific positional value.
Section: Positional functions
Get the second value in each partition
sql
sql
SELECT customer_id,
order_date,
amount,
NTH_VALUE(amount, 2) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_order_amount
FROM orders;Explanation
`NTH_VALUE` is ideal for milestone-style questions such as second purchase or third touch.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing Positional functions
Get the first value in each partition
Carry the earliest value across all rows in the partition.
Get the true last value in each partition
Use an explicit full frame with LAST_VALUE.
Compare current value to final partition value
Measure remaining distance to the last value.