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

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?

Martin,
I'm sure some more experienced SQL'r could put together an interesting Pattern on this, but here's my shot... I haven't tried this, but couldn't you:

1. Write the full query, including an ORDER BY clause ordering the result set by the desired attributes
2. Add to the WHERE clause a statement to limit results to ROWNUM <= 10

Just an untested guess...

Regards,

        Eric

Eric S. Kramer
Huntington Bancshares Incorporated
Columbus, OH USA
(614) 480-5088 Received on Sun Aug 13 1995 - 00:00:00 CEST