NTH_VALUE function in SQL - Step-by-Step Execution

Concept Flow - NTH_VALUE function

Start with ordered data

↓

Define window partition and order

↓

Locate Nth row in window

↓

Return value from Nth row

↓

If Nth row missing, return NULL

↓

End

The NTH_VALUE function picks the value from the Nth row in a defined window of ordered data. If that row doesn't exist, it returns NULL.

Execution Sample

SQL

SELECT department, employee, salary,
       NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC) AS second_highest_salary
FROM employees;

This query finds the second highest salary in each department and shows it alongside each employee.

Execution Table

Step	Row (department, employee, salary)	Window Partition	Order in Partition	NTH_VALUE(2) Result	Explanation
1	(Sales, Alice, 90000)	Sales	1 (highest salary)	85000	Second highest salary in Sales is 85000, returned for Alice
2	(Sales, Bob, 85000)	Sales	2	85000	Bob is second highest, so value is 85000
3	(Sales, Carol, 70000)	Sales	3	85000	Carol gets second highest salary 85000 as window is same
4	(IT, Dave, 120000)	IT	1	110000	Second highest salary in IT is 110000, returned for Dave
5	(IT, Eve, 110000)	IT	2	110000	Eve is second highest, value 110000
6	(IT, Frank, 95000)	IT	3	110000	Frank gets second highest salary 110000
7	(HR, Grace, 70000)	HR	1	NULL	Only one employee in HR, no second highest, returns NULL
8	End				No more rows to process

💡 All rows processed; NTH_VALUE returns NULL when Nth row does not exist in partition

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	After Row 7	Final
Current Row	None	(Sales, Alice, 90000)	(Sales, Bob, 85000)	(Sales, Carol, 70000)	(IT, Dave, 120000)	(IT, Eve, 110000)	(IT, Frank, 95000)	(HR, Grace, 70000)	None
Partition	None	Sales	Sales	Sales	IT	IT	IT	HR	None
Order Position	None	1	2	3	1	2	3	1	None
NTH_VALUE(2)	None	85000	85000	85000	110000	110000	110000	NULL	None

Key Moments - 3 Insights

Why does the NTH_VALUE function return the same value for all rows in a partition?

Because NTH_VALUE looks at the entire window partition and returns the value from the Nth row in the ordered set. This value is constant for all rows in that partition, as shown in execution_table rows 1-3 for Sales.

What happens if the partition has fewer rows than N in NTH_VALUE?

The function returns NULL because there is no Nth row to pick from. This is shown in execution_table row 7 for HR department.

Does NTH_VALUE depend on the current row's position in the partition?

No, it always returns the value from the fixed Nth row in the ordered partition, regardless of the current row's position, as seen in rows 4-6 for IT.

Visual Quiz - 3 Questions

Look at the execution_table, what is the NTH_VALUE(2) result for the row (Sales, Carol, 70000)?

A 70000

B 85000

C 90000

D NULL

💡 Hint

Check execution_table row 3 under 'NTH_VALUE(2) Result' column.

At which step does NTH_VALUE return NULL because the partition has fewer than 2 rows?

A Step 7

B Step 3

C Step 1

D Step 5

💡 Hint

Look at execution_table row 7 and the Explanation column.

If we change NTH_VALUE(salary, 3) in the query, what would be the NTH_VALUE result for (IT, Frank, 95000)?

A 120000

B 110000

C 95000

D NULL

💡 Hint

Refer to variable_tracker 'Order Position' and 'NTH_VALUE' values for IT partition rows.

Concept Snapshot

NTH_VALUE(expression, N) OVER (PARTITION BY ... ORDER BY ...)
- Returns the value of expression from the Nth row in the window.
- Window is defined by PARTITION BY and ORDER BY.
- If Nth row doesn't exist, returns NULL.
- Same value repeats for all rows in the partition.
- Useful to find Nth highest/lowest values in groups.

Full Transcript

The NTH_VALUE function in SQL returns the value from the Nth row of a window partition ordered by specified criteria. It first divides data into partitions, orders each partition, then picks the value from the Nth row in that order. If the partition has fewer than N rows, it returns NULL. The value returned is the same for every row in that partition. For example, to find the second highest salary per department, NTH_VALUE(salary, 2) is used with PARTITION BY department and ORDER BY salary DESC. This function helps to extract specific ranked values within groups in a table.

Practice

1. What does the SQL function NTH_VALUE(column, n) do when used with an OVER() clause?

easy

A. Returns the maximum value in the column.

B. Returns the total count of rows in the table.

C. Returns the value at the nth position in the ordered set of rows.

D. Returns the first value in the column without ordering.

Solution

1. Step 1: Understand the function purpose

   The NTH_VALUE function is designed to return a value from a specific position (n) in an ordered set of rows.

2. Step 2: Recognize the role of OVER()

   The OVER() clause defines how rows are ordered and grouped, which is essential for NTH_VALUE to pick the correct nth value.

3. Final Answer:

   Returns the value at the nth position in the ordered set of rows. -> Option C

4. Quick Check:

   NTH_VALUE returns nth value in order [OK]

Hint: NTH_VALUE picks the nth row's value after ordering [OK]

Common Mistakes:

• Confusing NTH_VALUE with aggregate functions like COUNT or MAX
• Ignoring the ordering inside OVER() clause
• Assuming it returns the nth row without ordering

2. Which of the following is the correct syntax to get the 3rd highest salary using NTH_VALUE in SQL?

easy

A. SELECT NTH_VALUE(salary, 3) OVER (PARTITION BY salary) FROM employees;

B. SELECT NTH_VALUE(salary, 3) FROM employees ORDER BY salary DESC;

C. SELECT NTH_VALUE(3, salary) OVER (ORDER BY salary DESC) FROM employees;

D. SELECT NTH_VALUE(salary, 3) OVER (ORDER BY salary DESC) FROM employees;

Solution

1. Step 1: Check the function parameters

   The correct order is NTH_VALUE(column, n), so salary first, then 3.

2. Step 2: Verify the OVER clause

   Ordering by salary descending (ORDER BY salary DESC) ensures the highest salaries come first, so the 3rd highest is the 3rd row.

3. Final Answer:

   SELECT NTH_VALUE(salary, 3) OVER (ORDER BY salary DESC) FROM employees; -> Option D

4. Quick Check:

   Syntax: NTH_VALUE(column, n) OVER (ORDER BY col DESC) [OK]

Hint: NTH_VALUE(column, n) needs OVER with ORDER BY for ranking [OK]

Common Mistakes:

• Swapping parameters inside NTH_VALUE
• Missing OVER() clause or ORDER BY inside it
• Using PARTITION BY incorrectly without ORDER BY

3. Given the table Scores with columns student and score, what will this query return?

SELECT student, score, NTH_VALUE(score, 2) OVER (ORDER BY score DESC) AS second_highest FROM Scores;

medium

A. Each row shows the student's score and the 2nd highest score overall repeated.

B. Each row shows the student's score and their own 2nd highest score.

C. The query returns only the row with the 2nd highest score.

D. The query returns an error because NTH_VALUE needs PARTITION BY.

Solution

1. Step 1: Understand the window function without PARTITION

   Since there is no PARTITION BY, the entire table is treated as one group ordered by score descending.

2. Step 2: Effect of NTH_VALUE(score, 2) OVER (ORDER BY score DESC)

   This returns the 2nd highest score value from the whole table, repeated for every row.

3. Final Answer:

   Each row shows the student's score and the 2nd highest score overall repeated. -> Option A

4. Quick Check:

   NTH_VALUE without PARTITION repeats nth value for all rows [OK]

Hint: Without PARTITION, NTH_VALUE repeats same nth value for all rows [OK]

Common Mistakes:

• Thinking NTH_VALUE filters rows to nth only
• Assuming PARTITION BY is mandatory
• Confusing NTH_VALUE with ROW_NUMBER or RANK

4. Identify the error in this SQL query:

SELECT employee_id, NTH_VALUE(salary, 1) OVER (PARTITION BY department_id) FROM employees;

medium

A. Missing ORDER BY clause inside OVER() causes error.

B. NTH_VALUE cannot be used with PARTITION BY.

C. The function parameters are in wrong order.

D. No error; query is correct.

Solution

1. Step 1: Check the OVER() clause requirements

   NTH_VALUE requires an ORDER BY clause inside OVER() to define the row order for nth value selection.

2. Step 2: Identify missing ORDER BY

   The query uses PARTITION BY but lacks ORDER BY, which causes a syntax error.

3. Final Answer:

   Missing ORDER BY clause inside OVER() causes error. -> Option A

4. Quick Check:

   NTH_VALUE needs ORDER BY in OVER() [OK]

Hint: Always include ORDER BY inside OVER() with NTH_VALUE [OK]

Common Mistakes:

• Omitting ORDER BY inside OVER()
• Thinking PARTITION BY alone orders rows
• Swapping function parameters

5. You want to find the 2nd earliest order date for each customer in the Orders table with columns customer_id and order_date. Which query correctly uses NTH_VALUE to achieve this?

hard

A. SELECT customer_id, order_date, NTH_VALUE(order_date, 2) FROM Orders GROUP BY customer_id;

B. SELECT customer_id, order_date, NTH_VALUE(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS second_earliest FROM Orders;

C. SELECT customer_id, order_date, NTH_VALUE(order_date, 2) OVER (ORDER BY customer_id, order_date ASC) AS second_earliest FROM Orders;

D. SELECT customer_id, NTH_VALUE(order_date, 2) FROM Orders ORDER BY order_date ASC;

Solution

1. Step 1: Use PARTITION BY to group by customer

   To find the 2nd earliest order per customer, partition rows by customer_id.

2. Step 2: Order each partition by order_date ascending

   Ordering by order_date ASC ensures earliest dates come first.

3. Step 3: Apply NTH_VALUE to get 2nd earliest date

   NTH_VALUE with n=2 picks the 2nd row's order_date in each partition.

4. Final Answer:

   SELECT customer_id, order_date, NTH_VALUE(order_date, 2) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS second_earliest FROM Orders; -> Option B

5. Quick Check:

   Partition by customer, order by date ASC, NTH_VALUE(2) [OK]

Hint: Partition by group, order ascending, then NTH_VALUE(n) [OK]

Common Mistakes:

• Missing PARTITION BY to group customers
• Ordering by wrong columns or descending order
• Using NTH_VALUE without OVER() clause