OraFAQ Forum: SQL & PL/SQL » How to get top 3 salary from Emp table deptno wise. in Oracle 8

Home » SQL & PL/SQL » SQL & PL/SQL » How to get top 3 salary from Emp table deptno wise. in Oracle 8

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get top 3 salary from Emp table deptno wise. in Oracle 8 [message #25072]

Mon, 24 March 2003 08:09

Suresh
Messages: 189
Registered: December 1998

Senior Member

Hi,
How to get top 3 salary from Emp table deptno wise...(For each deptno how to get the top 3 salary in Oracle 8)

Regards
Suresh N

Report message to a moderator

Re: How to get top 3 salary from Emp table deptno wise. in Oracle 8 [message #25074 is a reply to message #25072]

Mon, 24 March 2003 08:24

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_quote:
----------------------------------------------------------------------

Hi,
How to get top 3 salary from Emp table deptno wise...(For each deptno how to get the top 3 salary in Oracle 8)

Regards
Suresh N

----------------------------------------------------------------------
Another problem that analytic functions were born to solve:

SQL> SELECT ranked.dname
  2  ,      ranked.deptno
  3  ,      ranked.ename
  4  ,      ranked.ranking
  5  ,      ranked.sal
  6  FROM (SELECT d.deptno
  7        ,      d.dname
  8        ,      e.ename
  9        ,      e.sal
 10        ,      <a href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions35a.htm#1017917">DENSE_RANK()</a> OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) ranking
 11        FROM   emp   e
 12        ,      dept  d
 13        WHERE  e.deptno = d.deptno) ranked
 14  WHERE ranked.ranking <= 3
 15  ORDER BY ranked.deptno
 16  ,        ranked.ranking
 17  /
  
DNAME              DEPTNO ENAME         RANKING        SAL
-------------- ---------- ---------- ---------- ----------
ACCOUNTING             10 KING                1       5000
ACCOUNTING             10 CLARK               2       2450
ACCOUNTING             10 MILLER              3       1300
RESEARCH               20 SCOTT               1       3000
RESEARCH               20 FORD                1       3000
RESEARCH               20 JONES               2       2975
RESEARCH               20 ADAMS               3       1100
SALES                  30 BLAKE               1       2850
SALES                  30 ALLEN               2       1600
SALES                  30 TURNER              3       1500
  
10 rows selected.
  
SQL>

Notice, department twenty has four rows because four people earn the top three salaries.

Good luck,

A

Report message to a moderator

Re: How to get top 3 salary from Emp table deptno wise. in Oracle 8 [message #25076 is a reply to message #25074]

Mon, 24 March 2003 09:38

Suresh N
Messages: 7
Registered: March 2003

Junior Member

Hi,

Thanx for the response.. But i need the query with out using any functions (Rank , Dense_Rank)...

I need this in Oracle 8.0...

Suresh

Report message to a moderator

Re: How to get top 3 salary from Emp table deptno wise. in Oracle 8 [message #25098 is a reply to message #25072]

Tue, 25 March 2003 04:06

niranjan das
Messages: 14
Registered: October 2002

Junior Member

select deptno,max(sal) from emp group by deptno
union
select deptno,max(sal) from emp e
where sal<(select max(sal) from emp where deptno=e.deptno)
group by deptno
union
select deptno,max(sal) from emp e
where sal<( select max(sal) from emp e1 where sal<(select max(sal) from emp where deptno=e1.deptno))
group by deptno

Report message to a moderator

Previous Topic:	PL/SQL Tables and Collections
Next Topic:	Guys very strange problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:37:44 CDT 2024