How to find 3rd max value of a column using MAX function in sql server?

Question

Yesterday i had a question in an interview which i thought i could find answers here in SO...

How to find 3rd max value of a column using MAX function in sql server?

Consider the column to be

Wages
20000
15000
10000
45000
50000

Answer 1

Ok. Read that I must use the Max function:

With RankedWages As
    (
        Select Wage
            , Row_Number() Over( Order By Wage Desc ) As WageRank
        From Wages
    )
Select Wage
From RankedWages As W1
Where WageRank =    (
                    Select Max(W2.WageRank)
                    From RankedWages As W2
                    Where W2.WageRank <= 3
                    )

Btw, if the rule was that you had to use MAX (meaning anywhere), you could have chea..eh..found a clever workaround by doing:

;With RankedWages As
    (
        Select Wage
            , Row_Number() Over( Order By Wage Desc ) As WageRank
        From Wages
    )
Select Max(Wage)
From RankedWages As W1
Where WageRank = 3

Answer 2

Very UGLY but only using MAX

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

Personally I would have gone with

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

And then asked the interviewer why they would ever want a solution using MAX, when they can use built in functionality.

Answer 3

Without using MAX, this is what I can think:

SELECT MIN(Wages) FROM
(
    SELECT TOP 3 Wages FROM table ORDER BY Wages DESC;
) As tmp;

Select the table by finding the top 3 wages. Then select min from the previous result set.

UPDATE: Okay, just read it has to use MAX function. I agree with astander's answer.