OraFAQ Forum: SQL & PL/SQL » Second highest salary from emp table without using sub-query.

Home » SQL & PL/SQL » SQL & PL/SQL » Second highest salary from emp table without using sub-query.

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

Second highest salary from emp table without using sub-query. [message #8374]

Thu, 14 August 2003 00:45

pkmicf
Messages: 18
Registered: August 2002

Junior Member

I want a guery which gives the second highest salary from emp table.

Report message to a moderator

Re: Second highest salary from emp table without using sub-query. [message #8375 is a reply to message #8374]

Thu, 14 August 2003 02:12

Maaher
Messages: 7065
Registered: December 2001

Senior Member

use Rank() or Dense_Rank():

SQL> SELECT ename
  2       , sal
  3    FROM ( select ename
  4                , sal
  5                , rank() over ( order by sal desc ) r
  6             from emp
  7         ) emp
  8   where r = 2;

ENAME             SAL
---------- ----------
SCOTT            3000
FORD             3000

Why would you avoid a subquery? If you want, you can always create a view instead of using the inline view...

MHE

Report message to a moderator

Previous Topic:	Disadvantage of Using index hint
Next Topic:	Test Date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 19:06:15 CDT 2024