This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes How a single value is retrieved for the query? Query= to find the nth best element Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "How a single value is retrieved for the query? Query= to find the nth best element" Watch "How a single value is retrieved for the query? Query= to find the nth best element" New topic
Author

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

raghavender kotla
Greenhorn

Joined: Sep 20, 2012
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

Joined: Sep 21, 2006
Posts: 152

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

Joined: Sep 01, 2005
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

Joined: Sep 01, 2005
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Mar 01, 2009
Posts: 1728
    
  14

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.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How a single value is retrieved for the query? Query= to find the nth best element