This query is used to display nth best element. But the below query gives a single value.
Find the second best element.
| Sal |
| 3500 |
| 2500 |
| 2500 |
| 5500 |
| 7500 |
select SAL from EMPLOYEE E1 where
(N - 1) = (select count(distinct(SAL))
from EMPLOYEE E2
where E2.SAL > E1.SAL )
I analyzed the query and I get the condition where X=X or X=Y. I am thinking that it gives a list of values, but that doesn't happen. It only displays a single value. Can anyone explain me how the where condition works to display a single value.
On Oracle I'd use the RANK or DENSE_RANK functions (they do slightly different things and only you may know which one you want).
Analytic functions cannot be used in WHERE clause, so if you use the analytic variety, you'd have to compute the rank in a subquery and then filter the desired row(s) in the outer query's WHERE clause.
Edit: clarified the where clause goes to the outer query.