Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
raghavender kotla
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Eclipse IDE Java Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic