Re: General approach to "Top-x-items" selection?

Try this query:

select item, value
from imports_table i
where 10 > ( select count(*)

             from imports_table
             where i.value < value);

What it says is that I want to select an item and its import value only when there are less than 10 items in the same table of greater import value compared to it. This will give you the top 10 items in terms of value, and the query is general enough to be modified for other situations. If you have a large table, watch out for performance!

Ngo Lip Wee (email:lwngo_at_tmi.com.sg)
Tritech Microelectronics International

Martin Smith (MikeS_at_cpcug.org) wrote:
: We have a common requirement to produce reports on the top x items by
: some value within categories (and sometimes subcategories).
 

: Example--What are the top 10 items (in 1994 value) imported from each
: country in the world?
 

: I can't believe this is not a common need (not the problem, but
: simliar ones like the top 10 salesmen in each region or the top
: 10 salaries in each department.)
 

: Is there a general way to accomplish this using Oracle?
 

: Thanks!
Received on Sun Aug 13 1995 - 00:00:00 CEST