PL/SQL query

How to find the nth highest salary in a table ?

Please suggest.

any restrictions? Nice question.

I came up w/this...I'm a SQL Server guy but liked the ? so I took a stab...this is the main idea though...

...I was able to put it in t-SQL to make it more dynamic w/this...(I'm not sure what Oracle's = is to t-SQL_

Maybe you can take that approach and throw Oracle syntax @ it to make it work. If it's not a valid approach let me know what the guidelines are if you have any.

make your life easy, look at the following query

SELECT *
FROM (SELECT * FROM emp
ORDER BY sal DESC)
WHERE rownum<21 -- show the top 20 highest salaries
/

shafique, can you show me a more dynamic solution? Not trying to argue, we basically use the same approach, but I'd like to see how Oracle's equivalent to MS SQL's T-Sql looks. Just now getting into Oracle stuff so I'm interested, thx.

Yes, sure, for dynamic purpose you need to use bind variable that is defined in sql by using & sign with any variable name so when you run the query, oracle will prompt you to pass values at runtime. look at the following code :

SELECT *
FROM (SELECT * FROM emp
ORDER BY sal DESC)
WHERE rownum<&nth

If the column data type is character then enclose this variable into single quote, like that:

WHERE rownum<'&nth'

If you are looking for more information about the bind variable, please read the oracle sql documentation, provided by the oracle with no cost, just need to register at www.oracle.com no charge for registration either. Or visit http://tahiti.oracle.com (still requires registration at oracle)

great, thx man.

actually shafique, our approach has a hole. Say we want the 3rd highest salary and we have a table with this data....

750 is the 3rd highest salary but our queries would return 500 as the 3rd highest.

We should've used a select distinct somewhere maybe, not sure of the syntax off the bat.

You are right, my previous query could return duplicate values and we can eliminate those value using DISTINCT keyword.

As long as i understand your question so you are looking for a specific value(s) return by the result in specific order, you have to do a little bit more effort and it might not easy for you to write such a query. fro example the following query will return the employee(s) information whose salary is 3rd highest in the company:

select e.empno,e.ename,e.sal,e.deptno,e.job
from (select rownum row_count, sal
from ( select distinct sal
from emp
order by sal desc)) b, emp e
where b.row_count = 2
and b.sal = e.sal

the following query will return the employees information their salaries are positioned 1st, 2nd and 5th on the payroll chart.

select e.empno,e.ename,e.sal,e.deptno,e.job
from (select rownum row_count, sal
from ( select distinct sal
from emp
order by sal desc)) b, emp e
where b.row_count in (1, 2, 5)
and b.sal = e.sal
order by e.sal desc

Guys !!

Thanks a lot for the inputs. This is very informative !!!

Thanks again.