Home » SQL & PL/SQL » SQL & PL/SQL » co-related query
co-related query [message #198108] Sun, 15 October 2006 00:47 Go to next message
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
Re: co-related query [message #198123 is a reply to message #198108] Sun, 15 October 2006 07:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try searching the board. Try "three highest", "analytic functions", or "homework".

Ross Leishman
Re: co-related query [message #198128 is a reply to message #198108] Sun, 15 October 2006 11:20 Go to previous messageGo to next message
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

Re: co-related query [message #198148 is a reply to message #198128] Sun, 15 October 2006 19:14 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or, of course, if you are patient, someone will do your homework for you.
Re: co-related query [message #198322 is a reply to message #198128] Mon, 16 October 2006 10:38 Go to previous message
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.
Previous Topic: POSTPAID TO PREPIAD TRAFFIC
Next Topic: LONG Select statement
Goto Forum:
  


Current Time: Sat Dec 03 15:52:50 CST 2016

Total time taken to generate the page: 0.10288 seconds