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:
- Find the employees with top N distinct salaries.
- Find the lowest salary among the salaries fetched by the above query, this will give us the Nth highest salary.
- 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)
How to find Nth highest salary from a table?
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;
- To find the 2nd highest sal set n = 2
- To find the 3rd highest sal set n = 3 and so on.
Let’s check to find 3rd highest salary:
Output: