MySQL Query to Get First, Second, Third, Nth Highest Salary

MySQL Query to Get First, Second, Third, Nth Highest Salary

In this MySQL tutorial, we would love to share with you MySQL query to get first, second, and third, … nth highest salary from the employees table.

MySQL query to get/find the first, second, third, …nTh highest salary

Let, we have an employees table, which is having the following records −

idnamesalarydepartment
1Tom500000PHP
2Sam40000PHP
3Bob20000Web design
4Alen90000Java
5Jack110000Python

1: MySQL Query To Find First Highset Salary

SELECT name, MAX(salary) as salary FROM employee 

2: Find Second Highest/max salary using sub query and IN clause

 SELECT MAX(salary) 
FROM employees
WHERE salary NOT IN ( SELECT Max(salary) FROM employees);

3: Using subquery and < operator instead of IN clause to find second highest salary

 SELECT MAX(salary) 
From employees
WHERE salary < ( SELECT Max(salary) FROM employees);

4: To find second highest salary Using the LIMIT clause in query

SELECT salary 
FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;

5: If multiple employees has the same salary to find second highest salary

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary LIMIT 3,1);

6: MySQL query to get the third highest salary

SELECT * FROM employee ORDER BY salary DESC LIMIT 2,1;

7: How to find Nth highest salary from a table

SELECT salary FROM Employee  ORDER BY Salary DESC LIMIT n-1,1

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Vue JS, Angular JS, React Js, WordPress, and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *