select level,max(sal) from emp where level=&levelno connect by prior s

how to check the 3rd max salary from an employee table? One
of the queries used is as follows:

select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal).

Here in the sub query "select
count(distinct(sal)) from emp b
where a.sal<=b.sal" or
"select count(distinct(sal)) from emp b
where a.sal=b.sal" should reveal the same number of rows is
in't it? Can any one here please explain me how is this
query working perfectly.

However, there is another query to get the 3rd highest of
salaries of employees that logic I can understand. Pls find
the query below.
"select min(salary) from emp where salary in(select
distinct
top 3 salary from
emp order by salary desc)"

Please explain me how

"select sal from emp a where 3=(select
count(distinct(sal)) from emp b
where a.sal<=b.sal)" works

source:http://www.allinterview.com/showanswers/33264.html.

Thanks in advance
Regards,
Karthik.

Question Posted / s.panda

4 Answers
8377 Views
I also Faced
E-Mail Answers

Answer Posted / s.panda

select level,max(sal) from emp where level=&levelno
connect by prior sal>sal group by level

By entering the level no,Accordingly we can get the nth highest salay of a table.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is compilation error in pl sql?

957

How do I remove duplicates in two columns?

1029

Explain the difference between cursor declared in procedures and cursors declared in the package specification?

1115

What is the largest value that can be stored in a byte data field?

997

What is composite data type in pl sql?

1079

How can you get sql*loader to commit only at the end of the load file? : aql loader

992

Why we use stored procedure instead of query?

981

what is a relationship and what are they? : Sql dba

1073

What is sql injection vulnerability?

925

What is index example?

991

What is t sql used for?

1003

What is difference between inner join and cross join?

952

What are the different types of constraints?

1147

Why stored procedures are faster than query?

1006

what is 'mysqladmin' in mysql? : Sql dba

1009