SQL Reference - NTH

NTH_VALUE(expression, nth) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression [ASC | DESC] [NULLS { FIRST | LAST }])

expression: The column or expression that will be evaluated. This parameter represents the value you want to retrieve at the nth position within the defined partition or entire result set.
nth: The position within the result set to be taken. This is a positive integer that represents the row position within the ordered partition or result set.
partition by partition_expression: This divides the query result into different groups, or partitions, based on one or multiple columns. It’s optional and if it’s not included, the function will treat the entire result set as a single partition.
order by sort_expression: This determines the order of the data in each partition or the entire result set. The function will calculate the nth value based on this order.
asc | desc: These options determine whether the sort order is ascending (ASC) or descending (DESC). The default is ASC if it’s not specified.
nulls { first | last }: This determines the position of NULLs in the ordered partition or result set. If NULLS FIRST is specified, NULLs are sorted to the beginning; If NULLS LAST is specified, NULLs sort to the end.

Example

CREATE TABLE tests (
    id serial PRIMARY KEY,
    name varchar(100),
    score integer
);

INSERT INTO tests (name, score)
VALUES ('John', 90), ('Sandy', 85), ('Mia', 95), ('Ryan', 92), ('Sophia', 88);

SELECT name, score,
NTH_VALUE(name, 2) OVER(
    ORDER BY score DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    AS second_highest_score_name
FROM tests;

Output

name	score	second_highest_score_name
Mia	95	Ryan
Ryan	92	Ryan
John	90	Ryan
Sophia	88	Ryan
Sandy	85	Ryan

Explanation

In the example given, the NTH_VALUE function is used to find the name of the person with the second highest test score. By using the NTH_VALUE function with the parameters name, 2, the function will return the name for the second largest score in the list when ordered in descending order. The data is analyzed within the entire table (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). Therefore, ‘Ryan’ appears as the second highest scorer in each row in the second_highest_score_name column.

NTH_VALUE(expression, nth) OVER ([query_partition_clause] [order_by_clause] [windowing_clause])

expression: This parameter defines the column or expression which value needs to be returned from the Nth row. The expression can be a column, a scalar function, or a scalar subquery.
nth: This parameter specifies the position of the row in the ordered partition from which to return the value. An integer is expected in this parameter. The first row position is 1.
query_partition_clause: This optional clause is used to divide the result set into partitions (groups / subsets) upon which Oracle applies the NTH_VALUE function separately. It typically includes the PARTITION BY keyword followed by one or more columns.
order_by_clause: This clause determines the order in which rows in a partition are arranged. It can be used to order the partition by one or more columns and typically includes an ORDER BY keyword.
windowing_clause: This optional clause is used to further refine the group of rows over which the NTH_VALUE function is applied by specifying start and end points within the partition. It includes keywords ROWS, RANGE, or GROUPS, and specification of BETWEEN… and… or UNBOUNDED PRECEDING/FOLLOWING or CURRENT ROW.

Example

SELECT
  first_name,
  last_name,
  salary,
  NTH_VALUE(salary, 3) OVER (
    ORDER BY salary DESC
  ) AS Third_Highest_Salary
FROM employees

Output

| first_name | last_name | salary | Third_Highest_Salary |
| ---        | ---       | ---    | ---                  |
| Robert     | Smith     | 8000   | 6500                 |
| John       | Doe       | 7000   | 6500                 |
| Mary       | Johnson   | 6500   | 6500                 |
| Michael    | Williams  | 6000   | null                 |
| Linda      | Brown     | 5500   | null                 |
| William    | Davis     | 5000   | null                 |
| Elizabeth  | Miller    | 4500   | null                 |

Explanation

The NTH_VALUE function returns the nth value in a sorted set of values. In this specific query, the NTH_VALUE function is used to return the third highest salary from the ordered set of salaries descending, from the employees table. For the records that do not reach the third position in the ordered salary list, the function returns NULL.

NTH_VALUE( expression, nth ) OVER ( [ partition_by_clause ] order_by_clause )

expression: An expression that returns a value. The function will find the nth value of this expression in terms of the order given by the order_by_clause.
nth: The rank of the value to retrieve. This will determine which ranked value for the expression will be returned by the function. If nth is zero or negative, the function returns NULL.
partition_by_clause: Optional. This divides the result set into partitions (or groups) that share the same values in specified columns. The function operates independently on each partition.
order_by_clause: The clause that determines the order in which the data rows are ordered. The nth value is determined according to this ordering.

Example

CREATE TABLE Sales (
  Quarter INTEGER PRIMARY KEY,
  Revenue NUMERIC
);

INSERT INTO Sales (Quarter, Revenue)
VALUES (1, 100), (2, 200), (3, 300), (4, 400);

SELECT Quarter, Revenue,
       NTH_VALUE(Revenue, 2) OVER (ORDER BY Quarter) AS Second_Highest_Revenue
FROM Sales ORDER BY Quarter;

Output

Quarter | Revenue | Second_Highest_Revenue
--------|---------|-----------------------
1       |   100   |           200
2       |   200   |           200
3       |   300   |           200
4       |   400   |           200

Explanation

NTH_VALUE is a window function in SQLite that returns the Nth value of the selected column (in this case, “Revenue”), as specified by the argument passed to NTH_VALUE (2 in this case), considering an ordered sequence defined by the ORDER BY clause in the OVER() window function. In this example, the NTH_VALUE function returns the second highest revenue from the “Sales” table for each row, when data is ordered by “Quarter”.