Finding the nth highest salary in SQL is a common interview question and a practical task in database management. Here are several approaches to solve this problem, each with detailed explanations and examples. We'll assume we have a table `Employee` with columns `id`, `name`, and `salary`.

1. Using Subquery with `DISTINCT` and `LIMIT`/`OFFSET`

This approach involves using a subquery to first sort the salaries in descending order, then limiting the results to find the nth highest salary.

Example:

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET n-1;

Explanation:

1. `SELECT DISTINCT salary` : Select unique salaries from the `Employees` table.
2. `ORDER BY salary DESC` : Sort the salaries in descending order.
3. `LIMIT 1 OFFSET n-1` : Skip the first `n-1` salaries and then return the next one. The `LIMIT 1` ensures only one result is returned.

For example, to find the 3rd highest salary (`n = 3`):

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

2. Using `ROW_NUMBER()` Window Function

The `ROW_NUMBER()` window function assigns a unique sequential integer to rows within a partition of a result set.

Example:

SELECT salary
FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM Employees
) AS ranked_salaries
WHERE row_num = n;

Explanation:

1. Subquery : The subquery generates a list of salaries with a row number assigned to each salary in descending order.

• `ROW_NUMBER() OVER (ORDER BY salary DESC)` : Assigns a unique row number to each salary, ordered by salary in descending order.

2. Main Query : The main query filters the results to return the salary where the row number equals `n`.

For example, to find the 3rd highest salary (`n = 3`):

SELECT salary
FROM (
    SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM Employees
) AS ranked_salaries
WHERE row_num = 3;

3. Using `DENSE_RANK()` Window Function

The `DENSE_RANK()` function assigns ranks to rows in an ordered partition, where the same rank is assigned to rows with equal values.

Example:

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
) AS ranked_salaries
WHERE rank = n;

Explanation:

1. Subquery : The subquery generates a list of salaries with a rank assigned to each salary in descending order.

• `DENSE_RANK() OVER (ORDER BY salary DESC)` : Assigns ranks to each salary, with the same rank for equal salaries.

2. Main Query : The main query filters the results to return the salary where the rank equals `n`.

For example, to find the 3rd highest salary (`n = 3`):

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
    FROM Employees
) AS ranked_salaries
WHERE rank = 3;

4. Using `CROSS JOIN` with `DISTINCT` and `COUNT()`

This method involves self-joining the table and using `COUNT()` to find the nth highest salary.

Example:

SELECT DISTINCT e1.salary
FROM Employees e1
WHERE n-1 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employees e2
    WHERE e2.salary > e1.salary
);

Explanation:

1. `SELECT DISTINCT e1.salary` : Select unique salaries from the `Employees` table (alias `e1`).
2. Subquery : The subquery counts the number of distinct salaries in the table (alias `e2`) that are greater than the current salary in `e1`.
3. `WHERE n-1`: The main query filters for the salary in `e1` where there are exactly `n-1` salaries greater than it.

For example, to find the 3rd highest salary (`n = 3`):

SELECT DISTINCT e1.salary
FROM Employees e1
WHERE 2 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM Employees e2
    WHERE e2.salary > e1.salary
);

5. Using `NTILE()` Window Function

The `NTILE()` function distributes the rows in an ordered partition into a specified number of groups.

Example:

SELECT salary
FROM (
    SELECT salary, NTILE(n) OVER (ORDER BY salary DESC) AS ntile_rank
    FROM Employees
) AS ntile_salaries
WHERE ntile_rank = 1
ORDER BY salary ASC
LIMIT 1;

Explanation:

1. Subquery : The subquery partitions the salaries into `n` groups and assigns a `ntile_rank` to each group.  

• `NTILE(n) OVER (ORDER BY salary DESC)`**: Divides the rows into `n` groups.

2. Main Query : The main query filters for the group with `ntile_rank = 1` (the top group) and returns the lowest salary in that group.

• `ORDER BY salary ASC LIMIT 1` : Orders the top group by salary in ascending order and returns the smallest salary (i.e., the nth highest salary).

For example, to find the 3rd highest salary (`n = 3`):

SELECT salary
FROM (
    SELECT salary, NTILE(3) OVER (ORDER BY salary DESC) AS ntile_rank
    FROM Employees
) AS ntile_salaries
WHERE ntile_rank = 1
ORDER BY salary ASC
LIMIT 1;

Each of these approaches has its own strengths and is suitable for different scenarios. The choice of method can depend on the specific SQL database being used, the size of the dataset, and performance considerations.