Friday, March 19, 2010

FInd 3rd MAX salary in Sql Server?

Use the following...


DECLARE @Table TABLE(
        Wages FLOAT
)

INSERT INTO @Table SELECT 20000
INSERT INTO @Table SELECT 15000
INSERT INTO @Table SELECT 10000
INSERT INTO @Table SELECT 45000
INSERT INTO @Table SELECT 50000

--SELECT  MAX(Wages)
--FROM    @Table where Wages < (select MAX(Wages) from @Table where wages < (select MAX(Wages) from @Table))

SELECT  *
FROM    (
            SELECT  *,
                    ROW_NUMBER() OVER(ORDER BY Wages DESC) RowID
            FROM    @Table
        ) sub
WHERE   RowID = 3

No comments:

Post a Comment