12,763,226 members (34,992 online)
Rate this:
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 26-Jun-12 8:12am
srinvas689
Updated 28-Mar-16 4: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.

Rate this:

## 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.
losmac 26-Jun-12 18:30pm

Interesting...
+5!
Rate this:

## 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;
Rate this:

## Solution 7

This is a great question.

SQL Find nth highest salary
Andrew Cherednik 29-Oct-12 7:39am

The link you have provided is great. I still can't get my head around this correlated subquery business.
Bruno Sprecher 1-Feb-15 6:49am

The link is really great, thank you for sharing.
Bruno
Rate this:

## 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```
Rate this:

## 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.
v2
losmac 26-Jun-12 18:31pm

Nice explained, my 5!
Rate this:

## 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```
v2

Top Experts
Last 24hrsThis month
 OriginalGriff 235 Peter Leow 160 CPallini 145 ppolymorphe 130 Karthik Bangalore 100
 OriginalGriff 4,787 Peter Leow 3,554 ppolymorphe 2,923 Karthik Bangalore 2,814 Graeme_Grant 2,626