Above Answer is correct With small modification that it is a corelated su

Hi,

Can anybody please explain me the flow of the below query.
I am not able to understand how this query works.

This query is for finding the Nth highest salary..

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

If N = 2 then second highest salary will be the o/p.
If N = 3 then third highest salary will be the o/p.
and so on..

Thanks,
Nitin

Question Posted / avi

5 Answers
14823 Views
Deloitte, Ness Technologies, I also Faced
E-Mail Answers

Answer Posted / avi

Above Answer is correct With small modification that it is a
corelated sub query first it considers sal from a like a.sal
then it compares with all the sal in a sub query the result
will be the no. of counts.This count compares with N value
which matches will be the Nth sal.

Is This Answer Correct ?

5 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is the need of merge statement?

1032

What are variables in pl sql?

1156

What is the use of function in sql?

1085

What is full join?

947

What are few of the schema objects that are created using PL/SQL?

1026

What is the meaning of disabling a trigger?

1168

How do you rank data in sql?

996

What are the different sql languages?

1031

What does := mean in pl sql?

1038

How do you write an index?

962

What is a table partition?

1122

In pl/sql, what is bulk binding, and when/how would it help performance?

987

What is the execution plan in sql?

1016

How do you declare a variable in pl sql?

1019

What is informix sql?

972