Alternate Solution
CREATE TABLE `Employee` (
`ENAME` varchar(225) COLLATE utf8_unicode_ci NOT NULL,
`SAL` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`ENAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
To find the 6th highest salary
Query:
mysql> select * from ((select * from Employee
ORDER BY `sal` DESC limit 6 ) AS T)
ORDER BY T.`sal` ASC limit 1;
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: