Ravi S. Pasari

• Sep 2nd, 2006

 

You can use the following e.g.1. SELECT * FROM ( SELECT deptno, ename , sal , ROW_NUMBER() OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp )WHERE Top3 <= 3/2. This will give distinct values.SELECT * FROM ( SELECT deptno, ename , sal , DENSE_RANK) OVER ( PARTITION BY deptno ORDER BY sal DESC ) Top3 FROM emp )WHERE Top3 <= 3/

dineshchaini

 Profile Answers by dineshchaini 

• Sep 10th, 2006

 

Hi...

  If u want to find the nth record in salary basis then.. I thnik this will work

select * from(select * from emp order by sal desc) where rownum=<nth number>

chandra

• Sep 13th, 2006

 

the query is select * from sample where rownum=(select max(rownum) from sample)

rashid

• Sep 20th, 2006

 

u can use inline view

select sal from(select sal from emp order by sal desc) where rownum <5;

Santo

• Oct 9th, 2006

 

Here is the query,

suppose i have table called T1 with 2 columns say ID,SAL then

 i want to find out 3rd highest salary

select a.sal from t1 a
where 3 =(select count(distinct(b.sal)) from t1 b where a.sal <= b.sal);

with Regs

Santo

Suresh

• Oct 13th, 2006

 

select * from ( select rownum r, sal,name from (select * from emp order by sal)) where r=<n>

You need to give all column names in first inline view.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 9th, 2006

 

select * from emp where rownum <=9

minus

select * from emp where rownum <9

sankari

• Jan 9th, 2007

 

We can use dense_rank() built in. It will rank the result set based on the column which is given in the over() clause.

For ex) to get the 7th highest salary we can use the following query.

select * from(
select empno,ename,salary,dense_rank() over(order by salary desc) r
from emp )where r=7

I have used this in 10g..i am sure about the below versions.

sudhaker

 Profile Answers by sudhaker 

• Jan 17th, 2007

 

to find nth highest salary u can go with this query

select min(sal) from (select distinct sal from emp order by sal desc) where rownum<='&ar';

krishnaindia2007

 Profile Answers by krishnaindia2007 

• May 3rd, 2008

 

select max(sal) from emp a where &n = (select count(distinct sal) from emp where sal >= a.sal);

nade_rs

 Profile Answers by nade_rs 

• Jun 25th, 2011

 

select min(sal) from (select distinct sal from emp order by sal desc) where rownum<n;

diptisaxena

 Profile Answers by diptisaxena 

• Jul 15th, 2011

 

If we have a table EMP and having data as, if we want second highest sal i.e 5000 the the query will be:

Code
EMPNO ENAME SAL


1         A         5000


2         B         2000


3         C         10000


 


SELECT MIN(SAL) FROM (SELECT * FROM EMP ORDER BY SAL DESC)


WHERE ROWNUM <=2


 

Same as nth highest query we need to put that number instead of 2.

ravindra

• Jul 27th, 2011

 

Code
SELECT * FROM(SELECT ROW NUMBER AS rno emp.*FROM emp)WHERE RANK=&n;

Nihar

• Aug 26th, 2011

 

SELECT * FROM Emp A
WHERE &n=(SELECT COUNT(DISTINCT(Sal)) FROM EMP B WHERE A.Sal<=B.Sal);

anitha

• Aug 31st, 2011

 

Code
SELECT * FROM emp a WHERE n = (SELECT count(DISTINCT sal) FROM emp b WHERE a.sal<=b.sal)


 


//IN n place mention 1, 2,3...


 

Art11

 Profile Answers by Art11 

• Sep 27th, 2011

 

It is better to use analytic functions these days as they are faster and smarter and your managers will think high of you...

Code
Top n-paid IN ALL Depts - changer the orde BY dept AND sal DESC IF ant TO find out top paid IN each dept:


SELECT * FROM ( SELECT deptno, ename, sal


        , ROW_NUMBER() OVER (ORDER BY sal DESC, ename) AS row_num


        , RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) rank


        , DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) d_rank


       FROM emp )


--WHERE row_num  <= 4


/

rohitosu

 Profile Answers by rohitosu 

• Mar 13th, 2012

 

This is incorrect. It will not return anything. One of the possible answer is

Code
SELECT salary FROM (SELECT  rownum n, a.salary 


FROM 


(SELECT DISTINCT salary FROM person ORDER BY salary ) a) 


WHERE n = 5 ;

rohitosu

 Profile Answers by rohitosu 

• Mar 13th, 2012

 

This is a nice solution but it will not work correctly if any of the row has null values in it.

This is what I did. I specified n = 1 which means I want the highest salary. then I put the NULL values for this record in salary column. The record with NULL value always shows me as the highest salaried employee which is incorrect.

Neeraj Siddhey

• Mar 20th, 2012

 

This will work:-

Code
SELECT DISTINCT a.salary FROM emp a, emp b


WHERE 9=(SELECT DISTINCT count(b.salary) FROM emp b WHERE a.salary<=b.salary);

manisha

• May 27th, 2012

 

Use

Code
NVL(a.sal,0)<= NVL(b.sal,0)