Find 1st, 2nd/second, 3rd, ..Nth highest salary in MySQL

Find 1st, 2nd/second, 3rd, ..Nth highest salary in MySQL

To find or get 1st, 2nd, 3rd , and nth highest salary in mysql query; In this tutorial, we will learn how to select or get first, second, and third, … nth highest salary from the MySQL db table by w3school.

Let’s, have an employees table, which has the following records −

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

How to Get/find the first, second, third, …nTh highest salary in MySQL

To find or get the 1st or first, 2nd or second, 3rd or third & nth highest salary from db table in MySQL, you can use the subquery, where in, LIMIT clause and ORDER BY clause for that. Here are some approaches to find/get first, second, and third, … nth highest salary from the MySQL db table by w3school:

1: 1st Highest Salary in MySQL without Limit

You can use the following MySQL query to find the first or 1st highest salary from MySQL database table;

SELECT name, MAX(salary) as salary FROM employee 

2: 2nd Highest Salary in MySQL without Limit using sub query and IN clause

To find the second-highest salary in MySQL, you can use the subquery with in clause. Here’s how:

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

3: 2nd highest salary in mysql with subquery

Using subquery and < operator to find the second highest salary from MySQL database table;

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

4: Second highest salary in mysql using limit

Use limit clause to find the 2nd highest salary from MySQL database table;

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

5: Second Highest Salary Using DISTINCT and ORDER BY

If multiple employees have the same salary and find 2nd highest salary. So, you can use distinct and order by clause,

Here is an example to find the second highest salary in MySQL using distinct and order by:

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

6: 3rd highest salary in mysql

Here’s an example of a SQL query to find the 3rd or third-highest salary in a mysql: 

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

7: nth highest salary in mysql

To find the nth highest salary in MySQL, you can use the LIMIT clause with the ORDER BY clause. For example, you can use the following query:

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

Conclusion

That’s it; you have learned how to find or get first, second, and third, … nth highest salary from the MySQL db table by w3school.

Recommended MySQL Tutorials

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, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL 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 *