Guest

• Oct 14th, 2006

 

You can use this:-

  select e_name from emp where salary= (select min(e_salary) from (select e_salary from emp order by e_salary desc) Tab1 where rownum < 4)

The inner query gives the 3rd highest salary and the outermost query gives the name of the empoyee.

neerak

 Profile Answers by neerak Questions by neerak

• Oct 15th, 2006

 

select e_name from emp where salary= (select min(e_salary) from (select e_salary from emp order by e_salary desc) Tab1 where rownum < 4)

The inner query gives the 3rd highest salary and the outermost query gives the name of the empoyee.

I tried the above query suggested by a member but it does not work.

It gives the name of three persons while I wanted to know the name and salary of the employee getting the third highest salary.

I used minus clause which worked. But what if I try the above query. It seems to be correct syntactically then why it did not work?

Any suggestions?

Guest

• Oct 15th, 2006

 

select e_name from emp where salary= (select min(e_salary) from (select e_salary from emp order by e_salary desc) Tab1 where rownum < 4)

The inner query gives the 3rd highest salary and the outermost query gives the name of the empoyee.

I tried the above query suggested by a member but it does not work.

It gives the name of three persons while I wanted to know the name and salary of the employee getting the third highest salary.

I used minus clause which worked. But what if I try the above query. It seems to be correct syntactically then why it did not work?

Any suggestions?

atmaram

• Oct 16th, 2006

 

Thank's

subasini sahu

• Nov 17th, 2006

 

Hi,

Try this query

select e_name,e_salary from table_name a where 2=(select count(e_salary) from table_name b where a.e_salary<b.e_salary);

It will give the name and salary of Third largest salary person.

Thanks

Subasini

neerak

• Nov 17th, 2006

 

Hi Subasini,I tried the query but it results into no rows selected.Would you please define the logic behind the query?Thanks.

Murali

• Jan 21st, 2007

 

Hi,

Please try the following quiery.

 Select empname,sal from (Select * from emp order by sal desc)  where rownum=3;

Thanks,

Murali.

ora.nachs

 Profile Answers by ora.nachs 

• Feb 7th, 2007

 

hi,you can try on this, a small change from the first one.just add distinct clause....select ename,sal from emp where sal= (select min(sal) from (select distinct sal from emp order by sal desc) Tab1 where rownum < 4)

shravanam

 Profile Answers by shravanam Questions by shravanam

• Oct 24th, 2007

 

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

TRY THIS ONE JUST GIVE N VALUE '3'

sachin.nsit

 Profile Answers by sachin.nsit 

• May 21st, 2008

 

This query will work for the specific employee with the 3rd highest,4th or nth highest salary...

select * from (select empid,salary ,rownum as position from (select empid,salary     from employee order by sal desc) ) where position=3

myhotinterview

 Profile Answers by myhotinterview 

• Jun 3rd, 2008

 

I think we can also use this:

select emp_name from from <table_name> where rownum=3 order by salary desc

ashishpandey52

 Profile Answers by ashishpandey52 

• Nov 12th, 2008

 

select e_name,e_salary from emp where e_salary=(select min(e_salary)  from (select e_salary from emp order by sal desc )where rownum<4;