# How a single value is retrieved for the query? Query= to find the nth best element

raghavender kotla
Greenhorn
Posts: 1
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.

Bill Clar
Ranch Hand
Posts: 163
I put your query in the "code=sql" tag for easier read.

That said, I need more details such as what field "N" represents. What are you trying to accomplish? It looks like your querying employees based on their salary.

Fatih Keles
Ranch Hand
Posts: 182
This query is used to display nth best element. But the below query gives a single value.

As you said, this query is for a single element, not for top most nth elements!

Below is my test in Oracle Database

if you wish to find top N elements, you should use something like this

Fatih Keles
Ranch Hand
Posts: 182
Here is how your query works:

Consider n=6 then,
Select each element that has 5 other higher values then itself. Being the 6th one, there should be 5 other elements bigger than the 6th one.

Martin Vajsar
Sheriff
Posts: 3752
62
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.

chris webster
Bartender
Posts: 2407
33
Fatih Keles wrote:

Actually I think you can use the top-N query to get the value you want e.g. I have a table called EMPLOYEES, and I want the 3rd highest SALARY:

But if you're on Oracle, then you should try the analytical functions as Martin suggests. They're more flexible and you will learn something new.