Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
i have a table like this. i want to get the 2nd max salary with duplicate values also
 

 
empid empname salary
1 srinivas 20000
3 suresh 20000
4 kevin 30000
2 simon 35000
5 sai 30000
6 ben 30000
7 sirish 30000
 

the output should be
empid empname salary
4 kevin 30000
5 sai 30000
 
it should get only two duplicate 2nd max sal
Posted 26-Jun-12 8:12am
srinvas685
Comments
Sandeep Mewara at 26-Jun-12 13:42pm
   
1. What about 'ben' & 'sirish'?
2. Did you try anything so far?
Prasad_Kulkarni at 26-Jun-12 23:49pm
   
Rheal apti question
Member 11084497 at 16-Sep-14 9:15am
   
This is a very important question for interview.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Avoiding DB specific code (postgres offset/limit comes to mind) I get:
 
SELECT * FROM T1
WHERE SALARY =
        (SELECT MAX(SALARY) FROM T1
           WHERE SALARY <> (SELECT MAX(SALARY) FROM T1)
        );
 
Might not be super fast, but not really that slow either.
 
Doesn't extend too well to third, fourth etc but then you could use database specific offset / limit (rownum in oracle etc) in the inner join to select the nth salary.
 
And the data you provide has 4 people with 30000 salary and all would be returned.
  Permalink  
Comments
losmac at 26-Jun-12 18:30pm
   
Interesting...
+5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from tablename
)
 
select * from result where SecondMaximum=2;
  Permalink  
v2
Comments
Dhruvin Bhatt at 20-Jun-14 9:37am
   
with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from emp
)

select Top 2 * from result where SecondMaximum=2 order by id asc;
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

This is a great question.
 
This page has a fantastic explanation and answer to this problem:
 
SQL Find nth highest salary
  Permalink  
Comments
Andrew Cherednik at 29-Oct-12 7:39am
   
The link you have provided is great. I still can't get my head around this correlated subquery business.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

select * from Employee E inner join (select  salary, row_number() over (order by salary desc)
 as Rowno from Employee)T on
T.Salary=a.Salary  where T.Rowno=2
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

This is ugly, but I verified that it works:
 
SELECT Employees.*
FROM Employees
INNER JOIN (
SELECT TOP 1 * 
FROM ( 
   SELECT DISTINCT TOP 2 salary
   FROM Employees 
   ORDER BY salary DESC 
) AS innerTable 
ORDER BY salary ASC) joinTable ON Employees.salary = joinTable.salary
 
This is a SQL statement that is three layers deep for a reason. First, we get the top two salaries (innerTable). Then we order them ascending and take only one (joinTable). This gives us the salary of the second-highest paid person in the company. We then use this in the JOIN statement to only get the employees with this matching salary. You could also put this in the WHERE statement (dealer's choice).
 
To update this query to pick a different value (third-highest, fourth, etc.), simply change the number two in the inner-most SELECT. Whatever number you put there will be the place of the salary returned.
  Permalink  
v2
Comments
losmac at 26-Jun-12 18:31pm
   
Nice explained, my 5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

As Sandeep Mewara wrote, the most important thing is: What's about 'bin' & 'sirich'?
Why? Because 'kevin', 'sai', 'bin' and 'sirich' have the same salary, so, the MAX for the second value is equal: 30000.
You can easy to check it, using query to find duplicates:
Note: # near the table name means temporary table
SELECT [salary], COUNT([salary]) AS [CountOfSalary]
FROM #emp
GROUP BY [salary]
ORDER BY [salary] DESC
 
In my example, i use cursor[^]. The result is processed one row at a time untill MAX.n will be equal 2 (second MAX).
 
--declare variables
DECLARE @iMax INT
DECLARE @iCounter INT
DECLARE @retMax INT
DECLARE @iCount INT
 
--declare cursor
DECLARE salary_cursor CURSOR FOR
SELECT [salary], COUNT([salary]) AS [CountOfSalary]
FROM #emp
GROUP BY [salary]
ORDER BY [salary] DESC
 
--set initial values for variables
SET @iCounter = 0
SET @iMax = 2 
SET @iCount = 0
 
--open cursor
--get the second MAX
OPEN salary_cursor;
WHILE (@iCounter < @iMax AND @@FETCH_STATUS = 0)
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM salary_cursor INTO @retMax, @iCount 
   SET @iCounter = @iCounter + 1;
END
CLOSE salary_cursor;
DEALLOCATE salary_cursor;
 
--view all data for the second MAX
SELECT [empid], [empname], [salary]
FROM #emp
WHERE [salary] = @retMax
 

If you would like to write this query as a stored procedure[^] (SP), it is simply to do it.
CREATE PROCEDURE GetMAXnOfSalary
        @iMax INT = 1
--by default get MAX
AS
BEGIN
    -- Don't display the messages that shows the count of the number of rows affected
    SET NOCOUNT OFF;
 
    -- the rest of the body of SP is the same as above ;)

END
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 11

Select Max(sal) from salary where sal not in (select MAX(sal) from salar)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 10

select top 1 salary from(select top 2 salary from Table name order by salary )s order by salary
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 12

Select max(salary) from tablename where salary< (select max(salary) from tablename)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 13

SELECT * FROM Employee WHERE salary= (SELECT salary FROM (SELECT salary,ROW_NUMBER () OVER (Order by salary desc) NameOrder
FROM Employee ) temp
WHERE NameOrder=2)
  Permalink  
Comments
Wes Aday at 20-Jun-14 8:52am
   
The question was answered 2 years ago. Why are you answering it again?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 14

Select e1.ename, e1.sal FROM emp e1 where 1=(Select count(e2.sal) from emp e2 where e2.sal>e1.sal);
  Permalink  
Comments
Wes Aday at 16-Sep-14 9:57am
   
The question was answered 2 years ago. Why are you answering it again?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,466
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,897


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 16 Sep 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100