🧠 NTH_VALUE(): Get a Value from Any Position

Xorthax can now get the first and last values in a group. But what if he wants something in the middle?

“What is the 2nd value?” “What is the 3rd value?”

That’s what NTH_VALUE() does.

🚀 NTH_VALUE() + OVER()

NTH_VALUE() returns the value from a specific position in a sequence.

NTH_VALUE(column, n) OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

This works because:

• PARTITION BY defines the group
• ORDER BY defines the sequence
• The frame includes all rows in the group

📊 Example: Second Order Amount per Customer

🧾 Sample Data

SELECT CustomerID, OrderDate, Amount,
       NTH_VALUE(Amount, 2) OVER (
         PARTITION BY CustomerID
         ORDER BY OrderDate
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS SecondAmount
FROM Orders;

Result:

Each row now shows the value from the second position in its group.

🧠 What’s Happening Here?

PARTITION BY CustomerID creates a group for each customer.

ORDER BY OrderDate defines the sequence within each group.

NTH_VALUE(Amount, 2) looks at the second row in that sequence.

That value is then repeated for every row in the group.

If a group has fewer than n rows, the result will be null.

⚠️ Important: If you leave out the window frame, SQL only looks at rows up to the current row.

That means NTH_VALUE() may return different results for different rows—or null—instead of the true nth value in the group.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, NTH_VALUE(totalamount, 2) OVER (ORDER BY orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM orders ORDER BY orderdate;

➡️ Next Step

Now that you can pull values from specific positions, you’re ready to assign positions to rows.

Next, you’ll learn how to number rows.

Want to go deeper?