Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve 2nd highest salary from table emp  () 1 Vote
icon5.gif  How to retrieve 2nd highest salary from table emp [message #207328] Tue, 05 December 2006 02:43 Go to next message
oracle_faqs
Messages: 5
Registered: December 2006
Location: New Delhi
Junior Member

Hi All,
Plz help me out to how retrieve 2nd highest salary from table emp.
You can reply to my id: oracle_faqs@yahoo.co.in

Thanks

[Updated on: Tue, 05 December 2006 02:45]

Report message to a moderator

Re: How to retrieve 2nd highest salary from table emp [message #207330 is a reply to message #207328] Tue, 05 December 2006 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, we can reply to you here, where other people get to see the answer.
Re: How to retrieve 2nd highest salary from table emp [message #207331 is a reply to message #207328] Tue, 05 December 2006 02:52 Go to previous messageGo to next message
oracle_faqs
Messages: 5
Registered: December 2006
Location: New Delhi
Junior Member

You can retrieve 2nd highest salary by the following query:-

select * from emp e where 1=(select count(empno) from emp where sal>e.sal);
Re: How to retrieve 2nd highest salary from table emp [message #207336 is a reply to message #207330] Tue, 05 December 2006 03:07 Go to previous messageGo to next message
oracle_faqs
Messages: 5
Registered: December 2006
Location: New Delhi
Junior Member

if u know the answer other then my query which i had given just now then plz post it.

select * from emp e where 1=(select count(empno) from emp where sal>e.sal);
Re: How to retrieve 2nd highest salary from table emp [message #207341 is a reply to message #207330] Tue, 05 December 2006 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
SELECT * 
FROM (SELECT ename,empno,sal,row_number() over (order by sal desc) ord
      FROM  emp)
WHERE ord = 2;
icon6.gif  Re: How to retrieve 2nd highest salary from table emp [message #207381 is a reply to message #207328] Tue, 05 December 2006 05:27 Go to previous message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
select * from employee e where 1=(select count(emp_id) from employeee where salary>e.salary)
Previous Topic: How to form this query
Next Topic: Bukl collection using with REFcursor.
Goto Forum:
  


Current Time: Thu Dec 08 22:09:35 CST 2016

Total time taken to generate the page: 0.08030 seconds