How to find 3rd maximum or nth maximum salary in MySQL?
In this tutorial, we will discuss about two methods for finding nth maximum salary first one is using subquery and the second one is using the aggregate function. this is the most common question asked in interviews
SubQuery:
- A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.
- A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
- Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
Aggregate function:
aggregate function or aggregation function is a function where the values of multiple rows are grouped together to form a single summary value. Common aggregate functions include Average (i.e., arithmetic mean), Count.
Creat Table and insert some data:
/***creat table***/
CREATE TABLE `emp_salary` (
`ID` int(11) NOT NULL,
`emp_name` varchar(200) NOT NULL,
`emp_salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/***Insert some data***/
INSERT INTO `emp_salary` (`ID`, `emp_name`, `salary`) VALUES (NULL, 'rahul', '3500'), (NULL, 'sanni', '18000'),(NULL, 'sanni', '23000'),(NULL, 'rawat', '3500'),(NULL, 'ram', '22000');
Select Nth maiximum or highest salry
/*==========finding NTH maximum salary ==========*/
/*==using agrefgate function==*/
SELECT emp_name, salary
FROM emp_salary e1
WHERE N-1 = (SELECT COUNT(DISTINCT salary) FROM emp_salary e2
WHERE e2.salary > e1.salary);
/*==using subquery==*/
SELECT * FROM (SELECT * FROM `emp_salary` ORDER BY ID DESC LIMIT N) as t ORDER BY ID DESC LIMIT 1;
Select 3rd maximum salary
/*=====finding 3rd maximum salary using Agregate function=====*/
SELECT `emp_name`, `salary` FROM emp_salary
e1 WHERE 3-1 = (SELECT COUNT(DISTINCT `salary`) FROM emp_salary e2 WHERE e2.`salary` > e1.`salary`);
/*=====finding 3rd maximum salary using subquery======*/
SELECT * FROM (SELECT * FROM `emp_salary` ORDER BY ID DESC LIMIT 3) as t ORDER BY ID DESC LIMIT 1;