File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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: 140

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: 3438
    
  47

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: 1479
    
  11

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
 
Similar Threads
Getting totals of rows in SQL
find second largest value in a column
MS SQL Query
using aliases
adding up variables