# Thread: N th highest and lowest salary of an emp

1. Registered User
Join Date
Dec 2003
Posts
42

## Unanswered: N th highest and lowest salary of an emp

Hi,

iam new to oracle, iam unable to retrieve how to select the nth highest and nth lowest salary from employee table. Can you pls suggest for getting this one.

Thank you..

2. Registered User
Join Date
Jan 2004
Location
India
Posts
191
Hi,

for nth highest salary
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
for nth lowest salary
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal>=b.sal);

Thanks,

3. Registered User
Join Date
Jan 2003
Location
Vienna, Austria
Posts
102
SELECT DISTINCT(sal) SALARY
FROM EMP
WHERE ROWNUM = &N
ORDER BY DISTINCT(sal)

4. Registered User
Join Date
Apr 2004
Location
Posts
249
Rownum is created after the data is fetched.

So when you say, where rownum = 5, for example, this is what happens:

Oracle fetches the data, looks at the first row, assigns it a value of 1, then compares the predicate "rownum = 5". Clearly, this fails. Then, Oracle fetches the next row, assigns it a rownum of 1 again, then checks.

As you can see, this process will continue indefinitely and finally you get zero rows of data.

The only case where it succeeds is when the predicate is "rownum = 1".

As a side note, Oracle probably optimizes queries such as "rownum <> 1" and just returns zero rows.

Ravi

5. Registered User
Join Date
Dec 2003
Location
Oklahoma, USA
Posts
354
This is a Top-N analysis problem:

Code:
```SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
RANK() OVER
(ORDER BY SAL Desc NULLS LAST) AS Emp_Rank
FROM Emp
ORDER BY SAL Desc NULLS LAST)
WHERE Emp_Rank = 1;```
JoeB

6. Registered User
Join Date
Jan 2003
Location
China
Posts
38

## Simply like these

SELECT * FROM(SELECT DISTINCT sal SALARY FROM emp ORDER BY sal DESC) WHERE ROWNUM <= &N;

SELECT * FROM(SELECT DISTINCT sal SALARY FROM emp ORDER BY sal ASC) WHERE ROWNUM <= &N;
Last edited by 884813; 06-02-04 at 02:39.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•