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.

How to find 3rd maximum or nth maximum salary in MySQL?
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');
SQL
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;
SQL

Related Post

How to Convert numbers or Indian Currency into words in PHP

Explore Now...

How to filter or sanitize numbers from strings in PHP?

Explore Now...

How to delete multiple row by selected checkboxes using PHP and ajax

Explore Now...
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;
SQL

Leave a comment

Feature Posts