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

Yesterday i had a question in an interview which i 开发者_StackOverflow社区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

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.

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.

We can do something like this also, but i really think it is a very bad idea..

SELECT TOP 1 col1 
FROM dbo.temp
WHERE col1 NOT IN ( 
            SELECT TOP 2 MAX(col1) 
            FROM dbo.temp
            GROUP BY col1 
            ORDER BY col1 DESC) 
ORDER BY col1 DESC

SELECT *

FROM table_name temp1

WHERE (n) = (

SELECT COUNT( DISTINCT (temp2.field_name))

FROM table_name temp2

WHERE temp2.field_name > temp1.field_name

)

Here n=3 for 3rd max

select distinct wages 
from @table e1 
where (select count(distinct sal) from @table e2 where e1.wages <= e2.sal)=3;

select wages from table_name order by wages desc limit 2,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

select MAX(Salary) from ( select top 3 Salary from emp1 order by Salary asc ) as tmp

select min(salary) from (select salary from(select salary from table_name order by salary desc)where rownum<=n);

for nth highest salary.

Descrition:

It will first order the salary column in descending order then select first 3 salary,then it will select the minimum salary from selected first 3 salary which is the 3rd highest salary.