Nth Highest Point

    

Write an SQL query to fetch the Nth highest point from the table.

Input :-

Games table :-
ID is the Primary Key column for the Games table.

ID	Points
1	200
2	150
3	450
4	300
5	250

N=4

Output :-

NthHighestPoint(4)

200

Query :-

create function NthHighestPoint(N Int) Returns Int
Begin
    Declare M Int;
    set M=N-1;
    Return(
        select distinct Points from Games
        order by Points desc limit 1 offset M
    );
End

Explanation : - 

The provided function, ‘NthHighestPoint’,  takes an input parameter N (an integer) and returns the

Nth highest value of the ‘Points’ column from a table called "Games."

Here's an explanation of the function:

• The function begins by declaring a variable called M and assigning it the value of N-1. This is done because the query uses the ‘offset' clause to skip the first M rows and start from the (M+1)th row when retrieving the Nth highest point.
• The function executes a SQL query using the ‘select’ statement. It retrieves the distinct values from the ‘Points’ column in the "Games" table.
• The retrieved values are then sorted in descending order using the ‘order by’ clause, so that the highest point values appear first.
• The ‘limit 1’ clause ensures that only one row is returned by the query. This is necessary because we are interested in finding the Nth highest point, and if there are ties for the Nth position, we only want to retrieve one of those values.
• The ‘offset M’ clause is used to skip the first M rows (M = N-1) and start retrieving rows from the (M+1)th row. This effectively retrieves the Nth highest point from the sorted list of points.
• Finally, the function returns the Nth highest point value as the result.

To summarize, the function selects the distinct points from the "Games" table, sorts them in descending order, skips the first (N-1) points, and returns the Nth highest point value.