15,882,802 members
See more:
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
Updated 28-Mar-16 3:30am
Sandeep Mewara 26-Jun-12 13:42pm
1. What about 'ben' & 'sirish'?
2. Did you try anything so far?
Rheal apti question
Member 11084497 16-Sep-14 9:15am
This is a very important question for interview.

Solution 1

Avoiding DB specific code (postgres offset/limit comes to mind) I get:

SQL
```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.

Maciej Los 26-Jun-12 18:30pm
Interesting...
+5!

Solution 6

with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from tablename
)

select * from result where SecondMaximum=2;

v2
Dhruvin Bhatt 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;

Solution 8

SQL
```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```

Solution 2

This is ugly, but I verified that it works:

SQL
```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.

v2
Maciej Los 26-Jun-12 18:31pm
Nice explained, my 5!

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
SQL
```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).

SQL
```--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.
SQL
```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```

v2

Top Experts
Last 24hrsThis month
 Graeme_Grant 95 Pete O'Hanlon 60 OriginalGriff 60 Maciej Los 40 raddevus 40
 Pete O'Hanlon 2,000 OriginalGriff 1,475 Graeme_Grant 1,020 Richard Deeming 893 Dave Kreskowiak 694

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900