OraFAQ Forum: SQL & PL/SQL » co-related query

Home » SQL & PL/SQL » SQL & PL/SQL » co-related query

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

co-related query [message #198108]

Sun, 15 October 2006 00:47

ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi

Senior Member

hi all,
i want to get dept wise 1st three highest salaries in ascending order of dept_no and descending order by sal.

CLIENT NAME SAL DEPT_NO EMP JOB
------ ---------------- ---------- ---------- ---------- --------
C00005 Ravi Sreedharan 4000 20 5000 analyst
C00007 Sumant 14000 20 700 analyst
C00010 Rupali 90000 40 600 analyst
C00001 Ivan Bayross 150000 10 2000 clerk
C00009 Rupak 300000 20 200 clerk
C00003 Paramada Jaguste 149000 30 3500 clerk
C00012 Rupesh 80000 30 580 clerk
C00004 Basu Navindgi 400000 10 500 manager
C00008 Sushil 18000 40 200 manager
C00006 Rukmini 200000 40 500 manager
C00011 Ramesh 190000 10 3980 manager
C00002 Vandana Saitwal 19000 30 200 manager

thanxx

Report message to a moderator

Sun, 15 October 2006 07:42

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try searching the board. Try "three highest", "analytic functions", or "homework".

Ross Leishman

Report message to a moderator

Sun, 15 October 2006 11:20

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

SQL> ed
Wrote file afiedt.buf
 
  1  select sal,dept_no
  2  from ( select sal ,dept_no
  3         from emp_test
  4         group by dept_no , sal
  5         order by sal desc)
  6* where rownum <= 3
SQL> / 
 
       SAL    DEPT_NO
---------- ----------
    400000         10
    300000         20
    200000         40

[Updated on: Sun, 15 October 2006 11:20]

Report message to a moderator

Sun, 15 October 2006 19:14

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Or, of course, if you are patient, someone will do your homework for you.

Report message to a moderator

Mon, 16 October 2006 10:38

ebrian
Messages: 2794
Registered: April 2006

Senior Member

user52 wrote on Sun, 15 October 2006 11:20

  1  select sal,dept_no
  2  from ( select sal ,dept_no
  3         from emp_test
  4         group by dept_no , sal
  5         order by sal desc)
  6* where rownum <= 3
SQL> / 
 
       SAL    DEPT_NO
---------- ----------
    400000         10
    300000         20
    200000         40

This method would have problems if there is more than 3 DEPT_NO's with the same "high" salary. In addition, it would not
allow the displaying of multiple DEPT_NO's if there was a tie and the OP needed three distinct "high" salaries.

The OP should search the forum as rleishman suggested, this has been asked tons of times.

Report message to a moderator

Previous Topic:	POSTPAID TO PREPIAD TRAFFIC
Next Topic:	LONG Select statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 15:12:39 CST 2024