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 −
| id | name | salary | department |
|---|---|---|---|
| 1 | Tom | 500000 | PHP |
| 2 | Sam | 40000 | PHP |
| 3 | Bob | 20000 | Web design |
| 4 | Alen | 90000 | Java |
| 5 | Jack | 110000 | Python |
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.
