SQL query to find Nth highest salary from salary table

Question

SQL query to find Nth highest salary from salary table

will someone help me to know the nth highest salary from the salary table in MYSQL

+10

mysql

jauwad Aug 2 '12 at 6:15

source share

25 answers

babooney · Answer 1 · 2012-08-02T07:46:08+0000

Try this, n will be the nth element you want to return

SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC LIMIT n,1

user2102237 · Answer 2 · 2015-02-04T10:15:01+0000

If you want to find the nth salary from the table (here n should be any thing like the 1st or 2nd or 15th highest salary)

This is a query to find the nth salary:

 SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET (n-1)

If you want to find the 8th highest salary, the request should be:

 SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET 7

Note: OFFSET starts at position 0 and, therefore, rule N-1 is used here.

Omesh · Answer 3 · 2012-08-02T06:19:13+0000

To get the nth highest salary, you first need to sort the data using ORDER BY , and then select the nth highest record using LIMIT with OFFSET .

 SELECT DISTINCT(salary) AS salary FROM tbl_salary ORDER BY salary DESC LIMIT 1 OFFSET (n - 1);

Abhishek kulkarni · Answer 4 · 2013-03-20T09:54:37+0000

 SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

For each record processed by an external request, an internal query will be executed and will return how many records have records with a salary less than the current salary. If you are looking for the second highest salary, your request will stop as soon as the internal request returns N-1.

2787184 · Answer 5 · 2015-11-27T11:27:50+0000

find the highest salary

 select MAX(Salary) from Employee;

finding the second highest salary

Request-1

 SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

Request 2

 select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee )

finding the nth highest salary

Request-1

 SELECT * /*This is the outer query part */ FROM Employee Emp1 WHERE (N-1) = ( /* Subquery starts here */ SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

Request 2

 SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

nth highest salary using TOP keyword in SQL Server

 SELECT TOP 1 Salary FROM ( SELECT DISTINCT TOP N Salary FROM Employee ORDER BY Salary DESC ) AS Emp ORDER BY Salary

Find nth highest salary in MySQL

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

Find nth highest salary in SQL Server

 SELECT Salary FROM Employee ORDER BY Salary DESC OFFSET N-1 ROW(S) FETCH FIRST ROW ONLY

Find the nth highest earnings in Oracle using rownum

 select * from ( select Emp.*, row_number() over (order by Salary DESC) rownumb from Employee Emp ) where rownumb = n; /*n is nth highest salary*/

Find the nth highest salary in Oracle using RANK

 select * FROM ( select EmployeeID, Salary ,rank() over (order by Salary DESC) ranking from Employee ) WHERE ranking = N;

Joe g joseph · Answer 6 · 2012-08-02T06:25:50+0000

try the following:

 select MIN(sal) from salary where sal in (select sal from salary order by sal desc limit 9)

sel · Answer 7 · 2012-08-02T06:23:12+0000

if you want to specify the nth maximum, you can use the rank method.

To get the third maximum, use

 SELECT * FROM (SELECT @rank := @rank + 1 AS rank, salary FROM tbl,(SELECT @rank := 0) r order by salary desc ) m WHERE rank=3

Varun · Answer 8 · 2014-05-20T12:08:30+0000

Try this to find the 5th highest salary -

 SELECT DISTINCT(column name) FROM table ORDER BY (column name) desc LIMIT 4,1

for the nth salary -

 SELECT DISTINCT(column name) FROM table ORDER BY (column name) desc LIMIT n-1,1

I tried it on the phpmyadmin panel ..

Nilesh gosai · Answer 9 · 2014-09-06T09:33:21+0000

 SELECT * FROM employe e1 WHERE n-1 = ( SELECT COUNT(DISTINCT(e2.salary)) FROM employe e2 WHERE e2.salary > e1.salary) Where n = highest number of salary like 1,2,3

user3201336 · Answer 10 · 2015-12-14T15:33:41+0000

First sorting all the records, they consume a lot of time (imagine if the table contains millions of records). However, the trick is to improve linear search.

 SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(*) FROM ( SELECT DISTINCT(Emp2.Salary) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary LIMIT N))

Here, as soon as the internal query detects n different salary values that exceed the salary of the external query, it returns the result to the external query.

Mysql clearly mentioned this optimization at http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

The above query can also be written as

 SELECT * FROM Employee Emp1 WHERE (N-1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary LIMIT N)

Again, if queries are as simple as working on a single table and are for informational purposes only, you can limit the outermost query to return 1 record and run a separate query by placing the nth highest salary in where clause

Thanks to the decision of Abishek Kulkarni, on which this optimization is proposed.

Vik2696 · Answer 11 · 2018-05-15T06:36:42+0000

 select distinct(column_name) from table_name order by column_name desc limit (n-1),1;

user3143552 · Answer 12 · 2018-06-16T15:13:50+0000

MySQL query to find the N-th highest salary from the salary table (100% true)

CHOOSE Salary FROM EMPLOYEE ON CHARGE RESTRICTION DESC LIMIT N-1,1;

rajesh · Answer 13 · 2013-02-11T22:37:12+0000

 SELECT DISTINCT(column_name) FROM table_name ORDER BY column_name DESC limit N-1,1;

where N represents the first salary.

Third highest salary:

 SELECT DISTINCT(column_name) FROM table_name ORDER BY column_name DESC limit 2,1;

Aman maurya · Answer 14 · 2016-01-19T06:54:16+0000

The request for the nth highest record is as follows:

 SELECT * FROM (SELECT * FROM table_name ORDER BY column_name ASC LIMIT N) AS tbl ORDER BY column_name DESC LIMIT 1;

It is simple and easy to understand.

hazera · Answer 15 · 2016-03-03T12:37:59+0000

For the 4th highest salary:

select minus (salary) from (select a separate salary from hibernatepractice.employee e order by limit desc limit 4) as e1;

For the first salary:

select min (salary) from (select a separate salary from hibernatepractice.employee e order by pc desc limit n) as e1;

Faizal r · Answer 16 · 2016-03-08T10:06:11+0000

This will work. To find the nth maximum number

 SELECT TOP 1 * from (SELECT TOP nth_largest_no * FROM Products Order by price desc) ORDER BY price asc;

For the fifth highest number

 SELECT TOP 1 * from (SELECT TOP 5 * FROM Products Order by price desc) ORDER BY price asc;

Shourob datta · Answer 17 · 2016-12-06T16:31:56+0000

Salary table

 SELECT amount FROM salary GROUP by amount ORDER BY amount DESC LIMIT n-1 , 1

Or

 SELECT DISTINCT amount FROM salary ORDER BY amount DESC LIMIT n-1 , 1

selvan · Answer 18 · 2017-09-27T10:12:40+0000

To get the 2nd highest salary:

 SELECT salary FROM [employees] ORDER BY salary DESC offset 1 rows FETCH next 1 rows only

To get the Nth highest salary:

 SELECT salary FROM [employees] ORDER BY salary DESC offset **n-1** rows FETCH next 1 rows only

arvind soni · Answer 19 · 2018-02-21T18:17:17+0000

 SET @cnt=0; SELECT s.* FROM (SELECT ( @cnt := @cnt + 1 ) AS rank, a.* FROM one AS a ORDER BY a.salary DESC) AS s WHERE s.rank = '3';

arvind soni · Answer 20 · 2018-02-21T18:22:28+0000

 set @cnt=0; select s.* from (SELECT (@cnt := @cnt + 1) AS rank,a.* FROM one as a order by a.salary DESC) as s WHERE s.rank='3';

=> this is for the 3rd highest salary. for the nth, replace the value 3. for example, the 5th maximum:

 set @cnt=0; select s.* from (SELECT (@cnt := @cnt + 1) AS rank,a.* FROM one as a order by a.salary DESC) as s WHERE s.rank='5';

vignesh · Answer 21 · 2018-09-17T07:05:26+0000

Try this solution.

 select SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT salary ORDER BY salary DESC),',',3),',',-1) from employees

Somendra joshi · Answer 22 · 2018-10-24T17:50:28+0000

Let there be a salaries table containing

 +----------+--------+--------+ | emp | salary | deptno | +----------+--------+--------+ | ep1 | 10 | dp1 | | ep2 | 20 | dp2 | | ep3 | 30 | dp2 | | ep4 | 40 | dp1 | | ep5 | 50 | dp1 | | ep6 | 60 | dp3 | | ep7 | 70 | dp3 | +----------+--------+--------+

By nested queries: (where you can change the offset 0/1/2 ... for the first, second and third ... places respectively)

  select * from salaries as t1 where t1.salary = (select salary from salaries where salaries.deptno = t1.deptno ORDER by salary desc limit 1 offset 1);

or maybe by creating a rank: (where you can change the rank = 1/2/3 ... for the first, second and third ... places respectively)

 SET @prev_value = NULL; SET @rank_count = 0; select * from (SELECT s.*, CASE WHEN @prev_value = deptno THEN @rank_count := @rank_count + 1 WHEN @prev_value := deptno THEN @rank_count := 1 ELSE @rank_count := 1 END as rank FROM salaries s ORDER BY deptno, salary desc) as t having t.rank = 2;

Rishab goyal · Answer 23 · 2019-04-10T12:38:45+0000

 SELECT Salary FROM table ORDER BY Salary DESC LIMIT n,1

Abhijeet navgire · Answer 24 · 2019-10-14T10:13:46+0000

Here we can create a MYSQL function for this. nth the highest salary from the Employee table.

 +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

For example, given the Employee table above, the nth highest salary, where n = 2, is 200. If there is no nth highest salary, the query should return zero.

 CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE limitv INT; SET limitv = N - 1; RETURN ( Select IFNULL( (select Distinct Salary from Employee order by Salary Desc limit limitv, 1), NULL ) as getNthHighestSalary ); END

Dipak burnwal · Answer 25 · 2014-06-17T11:52:49+0000

If you want to get all records of employees having the third highest salary, you can use this sql :

Table Name: Salary

 select * from salary where salary = (select distinct salary from salary order by salary desc limit 2,1)

SQL query to find Nth highest salary from salary table

More articles: