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
8212 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

Explain clause in sql?

958

What is pl sql record in oracle?

968

Where the sql database files are stored?

956

What is the use of partition by in sql?

928

Can we use pl sql in sql server?

1011

Name some usages of database trigger?

965

What are the set operators in sql?

1027

Is sql injection illegal?

963

How does sql developer connect to oracle database?

949

what is dbms? : Sql dba

931

What are field types?

939

How do I run a sql trace?

905

How many types of primary keys are there?

977

What is the purpose of design view?

950

What is sql injection vulnerability?

869