• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 163
Eclipse IDE Tomcat Server Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic