OraFAQ Forum: SQL & PL/SQL » nested sql statement

Home » SQL & PL/SQL » SQL & PL/SQL » nested sql statement

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

nested sql statement [message #2179]

Wed, 26 June 2002 04:30

avinash
Messages: 22
Registered: February 2001

Junior Member

In the user, there is a table emp having 2 columns deptno, sal. Now, i want to find out top 3 highly paid employees, dept-wise, using sql. Max function should now be used.

Report message to a moderator

Re: nested sql statement [message #2184 is a reply to message #2179]

Wed, 26 June 2002 08:32

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> select ename, deptno, sal, ranked 
  2   from 
  3  ( select ename, deptno, sal, 
  4  rank() over (partition by deptno order by sal desc) as ranked 
  5   from emp) 
  6  where ranked <=3 
  7  /

ENAME          DEPTNO        SAL     RANKED
---------- ---------- ---------- ----------
KING               10       5000          1
CLARK              10       2450          2
MILLER             10       1300          3
SCOTT              20       3000          1
FORD               20       3000          1
JONES              20       2975          3
BLAKE              30       2850          1
ALLEN              30       1600          2
TURNER             30       1500          3

9 rows selected.

Report message to a moderator

Previous Topic:	Ref Cursors
Next Topic:	sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 20:01:14 CDT 2024