Alternate Solution

Suppose the task is to find the employee with the Nth highest salary from the above table. We can do this as follows: 

1. Find the employees with top N distinct salaries.
2. Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.
3. Find the details of the employee whose salary is the lowest salary fetched by the above query.

Query:  

SELECT * FROM Employee WHERE sal = 
         (
            SELECT MIN(sal) FROM Employee 
            WHERE  sal IN (
            SELECT DISTINCT TOP N
            sal FROM Employee 
            ORDER BY sal DESC
))

The above query will fetch the details of the employee with the Nth highest salary. Let us see how: 

Consider N = 4.

Starting with the most inner query, the query:

Query:

SELECT DISTINCT sal
FROM Employee
ORDER BY sal DESC
LIMIT 4;

Output:

Query:

SELECT MIN(sal) FROM Employee WHERE sal IN (
  SELECT DISTINCT sal
  FROM Employee
  ORDER BY sal DESC
  LIMIT 4
);

Output:

You can see that the above-returned result is the required 4th highest salary.

Another Solution:

Here N = nth Highest Salary eg. 3rd Highest salary: N=3.

Syntax:

SELECT ename,sal from Employee e1 where 

N-1 = (SELECT COUNT(DISTINCT sal)from Employee e2 where e2.sal > e1.sal) 

Structured Query Language is a computer language that we use to interact with a relational database. Finding Nth highest salary in a table is the most common question asked in interviews. Here is a way to do this task using the dense_rank() function. 

Consider the following table: 

Employee:

CREATE TABLE:

CREATE TABLE emp (
  emp_name VARCHAR(50),
  emp_salary DECIMAL(10,2)
);

Let’s insert some random data with a random name and then we will look at how to calculate the nth highest emp_salary.

Query:

CREATE TABLE emp (
  emp_name VARCHAR(50),
  emp_salary DECIMAL(10,2)
);
INSERT INTO emp (emp_name, emp_salary) VALUES
('Shubham Thakur', 50000.00),
('Aman Chopra', 60000.50),
('Naveen Tulasi', 75000.75),
('Bhavika uppala', 45000.25),
('Nishant jain', 80000.00);

Output:

Query:

SELECT * FROM (
  SELECT emp_name, emp_salary, DENSE_RANK() OVER (ORDER BY emp_salary DESC) AS r
  FROM emp
) AS subquery
WHERE r = 3;

1. To find the 2nd highest sal set n = 2
2. To find the 3rd highest sal set n = 3 and so on.

Let’s check to find 3rd highest salary:

Output: