12,079,202 members (45,860 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
srinvas688
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
Rate this:

## Solution 11

Select Max(sal) from salary where sal not in (select MAX(sal) from salar)
Rate this:

## Solution 10

`select top 1 salary from(select top 2 salary from Table name order by salary )s order by salary`
Rate this:

## Solution 12

Select max(salary) from tablename where salary< (select max(salary) from tablename)
Rate this:

## 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)

The question was answered 2 years ago. Why are you answering it again?
Rate this:

## Solution 14

Select e1.ename, e1.sal FROM emp e1 where 1=(Select count(e2.sal) from emp e2 where e2.sal>e1.sal);

The question was answered 2 years ago. Why are you answering it again?
Rate this:

## Solution 16

```SELECT     EmployeeID, FirstName, LastName, Salary, JoiningDate, Department, Gender
FROM         Employee_Detail
WHERE     (Salary =
(SELECT     MAX(Salary) AS Expr1
FROM          Employee_Detail AS Employee_Detail_2
WHERE      (Salary =
(SELECT     MAX(Salary) AS Expr1
FROM          Employee_Detail AS Employee_Detail_1))))```

The question was answered 3 years ago. Why are you answering it again?
Rate this:

## Solution 17

```select * from TBL_Employees
where
salary in (
select top 1 (salary) from (
(select distinct top 4 salary from TBL_Employees order by salary desc )
) as innerTable order by salary asc
)```
CHill60 7-Jan-16 10:49am

Question was asked and answered over 3 years ago. And this solution doesn't actually return the 2nd maximum salary!

Top Experts
Last 24hrsThis month
 Dave Kreskowiak 525 ppolymorphe 315 CHill60 200 CPallini 185 OriginalGriff 170
 Dave Kreskowiak 3,551 OriginalGriff 3,170 Richard MacCutchan 2,644 CPallini 1,907 CHill60 1,779