sql query to find 2nd maximum salary

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

select * from result where SecondMaximum=2;

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

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.

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