Home » SQL & PL/SQL » SQL & PL/SQL » higest salary
higest salary [message #210874] Fri, 22 December 2006 20:39 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello team

sir i want to get deptwise first three highest salary but unable to get it .please look on my query.
i got deptwise salary through group by but what should i do in further step ???

SQL> ed
Wrote file afiedt.buf

  1  select * from emp1
  2  where (dept_no, sal) in(
  3  select dept_no, sal from emp1
  4* group by (dept_no, sal))
SQL> /

CLIENT NAME                    SAL    DEPT_NO        EMP JOB      HIREDATE
------ ---------------- ---------- ---------- ---------- -------- ---------
C00001 Ivan Bayross         165000         10       2000 clerk    12-DEC-01
C00001 Ivan Bayross         165000                  2000 clerk    12-DEC-01
C00011 Ramesh               209000                  3980 manager  29-FEB-04
C00017 Poonam               390000                   300 manager
C00004 Basu Navindgi        440000                   500 manager  12-FEB-01
C00005 Ravi Sreedharan        4800         20       5000 analyst  12-JUN-00
C00007 Sumant                16800                   700 analyst  12-SEP-02
C00009 Rupak                360000                   200 clerk    01-JAN-06
C00016 Ishika               367900                   800 supreme
C00013 Ritika               451838                   390  supreme
C00013 Ritika               451838                   390 supreme
C00005 Ritika               451838                   390 supreme
C00018 Ashish               459902                   450 manager
C00012 Rupesh               104000         30        580 clerk
C00003 Paramada Jaguste     193700                  3500 clerk    12-FEB-03
C00003 Paramada Jaguste     193700                  3500 clerk    12-FEB-03
C00015 mamoom               260000                  4980 supreme
C00014 Shiva                780000                   300 manager
C00008 Sushil                25200         40        200 manager  02-JUL-06
C00010 Rupali               126000                   600 analyst  25-DEC-05
C00006 Rukmini              280000                   500 manager  12-JUN-04

21 rows selected.


i want to get dept wise first three highest salary

with regards
ishika
Re: higest salary [message #210881 is a reply to message #210874] Fri, 22 December 2006 21:23 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Use this first rest I will solve then tell you , Using Plsql it is possible.

Quote:
select * from emp
where (deptno, sal) in(
select deptno, sal from emp
group by (deptno, sal))
order by deptno,sal desc


Bye

Re: higest salary [message #210892 is a reply to message #210881] Sat, 23 December 2006 00:35 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

search for analytical function or just search this forum and you will come across a bunch of such queries.




regards,
Re: higest salary [message #210895 is a reply to message #210892] Sat, 23 December 2006 00:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Learn to search the board.

http://www.orafaq.com/forum/fa/448/0/

MHE
Previous Topic: to get distict values
Next Topic: Exception problem (merged)
Goto Forum:
  


Current Time: Thu Dec 08 19:58:28 CST 2016

Total time taken to generate the page: 0.12950 seconds