how to find out 3rd max salary from an emp table?

[saikrishna cinux]

please can any one provide me an easy understandable query for finding out 3rd max salary from emp table?

thanks in advance



saikrishna

[Srinivasa Raghavan]

This query should work for oracle.

Select * From (Select * From Emp Order by Salary desc ) where RowId = 3

[saikrishna cinux]

sorry it's not working oracle sql

any other queries please

[Shailesh Chandra]

Try this

SELECT * FROM (SELECT DEPTNO,
       ENAME,
       SALARY,
       DENSE_RANK() OVER (ORDER BY SALARY DESC) "RANK"
FROM   EMP
)
WHERE RANK=3

Shailesh

[steve souza]

I didn't test, but this may work. It is finding the max value in the table, and then finding the max value that is less than that, and doing it one more time again. Note if the 3rd highest value has the max value too this query wouldn't indicate that. That may or may not be what you want.

 
select * from table where  col<
         (select max(col) from table where col < 
            (select max(col) from table))
   

With sybase (and possibly other db's) you could use set rowcount, and order by desc. The 3rd row would be your value.

  
set rowcount 3
select * from table order by col desc