|
|
| Re: Top 2nd salary [message #1696 is a reply to message #1682] |
Thu, 23 May 2002 02:08  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Try the following:
You select the top-3 salaries minus the top-1 salary.
Beware of double salaries!
SELECT empno
, sal
from (select empno, sal
from emp
order by sal desc
)
where rownum < 4
minus
SELECT empno
, sal
from (select empno, sal
from emp
order by sal desc
)
where rownum < 2
/
======
to include duplicate salaries, try the following (it's from the top of my head, so it is possible that there's a more simple solution):
select e.empno
, e.sal
from emp e
, ( SELECT sal
FROM ( SELECT distinct(e.sal)
FROM emp e
ORDER BY 1 desc
)
WHERE rownum < 4
) f
where e.sal = f.sal
minus
select e.empno
, e.sal
from emp e
, ( SELECT sal
FROM ( SELECT distinct(e.sal)
FROM emp e
ORDER BY 1 desc
)
WHERE rownum < 2
) f
where e.sal = f.sal
order by 2 desc
/
=========
Regards,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
