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 @iMax INT
DECLARE @iCounter INT
DECLARE @retMax INT
DECLARE @iCount INT
DECLARE salary_cursor CURSOR FOR
SELECT [salary], COUNT([salary]) AS [CountOfSalary]
FROM #emp
GROUP BY [salary]
ORDER BY [salary] DESC
SET @iCounter = 0
SET @iMax = 2
SET @iCount = 0
OPEN salary_cursor;
WHILE (@iCounter < @iMax AND @@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM salary_cursor INTO @retMax, @iCount
SET @iCounter = @iCounter + 1;
END
CLOSE salary_cursor;
DEALLOCATE salary_cursor;
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
AS
BEGIN
SET NOCOUNT OFF;
END