Can anyone explain how the below query will work internally in the oracle i am confused .
This is the query to find the n th maximum salary
SELECT DISTINCT SAL FROM EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL)
FROM EMP B WHERE A.SAL<=B.SAL);
when i am try to split the query into two and then i execute this the result will be in below.
SQL> select count(distinct sal) from emp where 5000<=5000;
COUNT(DISTINCTSAL)
------------------
12
SQL> ed
Wrote file afiedt.buf
1* select count(distinct sal) from emp where 1300<=1300
SQL> /
COUNT(DISTINCTSAL)
------------------
12
My question here is how it will take the 2nd maximum salary when i given the n th value as 2.
Values in the table will be
SQL> select sal from emp;
SAL
----------
800
1600
1250
2975
1250
2850
2450
3000
5000
1500
1100
SAL
----------
950
3000
1300
16 rows selected.
] 
Blog
Search
Help
Members
Register
Login
Home
