Home » SQL & PL/SQL » SQL & PL/SQL » nested sql statement
nested sql statement [message #2179] Wed, 26 June 2002 04:30 Go to next message
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.
Re: nested sql statement [message #2184 is a reply to message #2179] Wed, 26 June 2002 08:32 Go to previous message
Mahesh Rajendran
Messages: 10690
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.
Previous Topic: Ref Cursors
Next Topic: sql
Goto Forum:
  


Current Time: Sun Jul 21 01:43:56 CDT 2019