Home » SQL & PL/SQL » SQL & PL/SQL » selecting 2nd largest salary
selecting 2nd largest salary [message #149685] Fri, 02 December 2005 23:16 Go to next message
esteem4305
Messages: 13
Registered: November 2005
Location: India
Junior Member

i have a table called employee.it's contents are

EMPCOD SALARY
------ ----------
c2312 10000
c3433 5400
c4433 2000
c1211 1300
c7899 78000

---If i want to select the highest salary how will i do??

---If i want to select the 2nd highest salary how should i do??


Help is appreciated.



Re: selecting 2nd largest salary [message #149691 is a reply to message #149685] Fri, 02 December 2005 23:57 Go to previous messageGo to next message
amolinaro
Messages: 24
Registered: September 2005
Junior Member
Hi,
there's actually many ways to do this, here's a few.
 -- if you're on a version that supports window functions
 -- you can use a function like ROW_NUMBER OVER or 
 -- DENSE_RANK OVER, depending on how you want to 
 -- treat ties.  
 -- consider the standard EMP table:

SQL> select empno,sal from emp order by 2 desc;

     EMPNO        SAL
---------- ----------
      7839       5000
      7788       3000
      7902       3000
      7566       2975
      7698       2850
      7782       2450
      7499       1600
      7844       1500
      7934       1300
      7521       1250
      7654       1250
      7876       1100
      7900        950
      7369        800

14 rows selected.

/* top sal */

SQL> ed
Wrote file afiedt.buf

  1  select empno,sal
  2    from (
  3  select empno,sal,
  4         row_number()over(order by sal desc) rn
  5    from emp
  6         ) x
  7*  where rn = 1
SQL> /

     EMPNO        SAL
---------- ----------
      7839       5000

-- you can do the same to find the second highest sal:

SQL> ed
Wrote file afiedt.buf

  1  select empno,sal
  2    from (
  3  select empno,sal,
  4         row_number()over(order by sal desc) rn
  5    from emp
  6         ) x
  7*  where rn = 2
SQL> /

     EMPNO        SAL
---------- ----------
      7788       3000

notice tho, in the sample data, there are 2 emps with
sal of 3000, so, if you wanna allow ties, use 
a function like DENSE_RANK OVER:

SQL> ed
Wrote file afiedt.buf

  1  select empno,sal
  2    from (
  3  select empno,sal,
  4         dense_rank()over(order by sal desc) rn
  5    from emp
  6         ) x
  7*  where rn = 2
SQL> /

     EMPNO        SAL
---------- ----------
      7788       3000
      7902       3000

-- If you're on oracle 8.0.x or earlier,
-- you'd have to use different techniques
-- such as:

SQL> ed
Wrote file afiedt.buf

  1  select empno,sal
  2    from emp
  3*  where sal = (select max(sal) from emp)
SQL> /

     EMPNO        SAL
---------- ----------
      7839       5000

-- and

SQL> ed
Wrote file afiedt.buf

  1  select empno,sal
  2    from emp
  3   where sal != (select max(sal) from emp)
  4     and empno not in (
  5  select distinct e.empno
  6    from emp e, emp d, emp f
  7   where e.sal < d.sal
  8     and d.sal < f.sal
  9* )
SQL> /

     EMPNO        SAL
---------- ----------
      7788       3000
      7902       3000

-- OR

SQL> ed
Wrote file afiedt.buf

  1  select e.empno,e.sal
  2    from emp e
  3   where 1 = (select count(*)
  4                from emp d
  5*              where d.sal > e.sal)
SQL> /

     EMPNO        SAL
---------- ----------
      7788       3000
      7902       3000



There's tons of ways to write queries like these,
what's best depends on your specific situation.

good luck,
Anthony
icon7.gif  Re: selecting 2nd largest salary [message #149693 is a reply to message #149685] Sat, 03 December 2005 00:01 Go to previous messageGo to next message
oraraj
Messages: 8
Registered: December 2005
Junior Member
Hi,

If you want to select the highest and the second highest salary salary, following is the query.I am assuming it has department number.

Select *
From
(Select deptno, ename, sal,
rank() OVER (Partition by deptno Order by sal desc ) rnk
From Employee)
Where rnk <= 2
Order By deptno, ename

RK
icon11.gif  Re: selecting 2nd largest salary [message #232733 is a reply to message #149691] Mon, 23 April 2007 02:29 Go to previous messageGo to next message
manish5.k
Messages: 9
Registered: September 2006
Junior Member
select e.empno,e.sal
from emp e
where 1 = (select count(*)
from emp d
where d.sal > e.sal)

There is one problem with this method of retrieving nth largest.

eg: table : emp(sal) Data : 50,40,40,30.

To get 2nd largest:-

select e.sal
from emp e
where 1 = (select count(*)
from emp d
where d.sal > e.sal)
result :
40
40

///
To get 3rd largest:-

select e.sal
from emp e
where 2 = (select count(*)
from emp d
where d.sal > e.sal)

result : no rows selected



Re: selecting 2nd largest salary [message #232743 is a reply to message #232733] Mon, 23 April 2007 02:52 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Let's get this old thread some sleep, shall we?

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

Closed.

MHE
Previous Topic: select first 50 records
Next Topic: Recent Joining records
Goto Forum:
  


Current Time: Fri Dec 09 13:57:43 CST 2016

Total time taken to generate the page: 0.10543 seconds