Q
Problem solve Get help with specific problems with your technologies, process and projects.

Top N subcategories, Part 4

I want a query to summarize categories of information so that the top N subcategories within each major category...

are displayed. All category codes and associated elements are in the same table.

The three-part answer that ended with Top N subcategories, Part 3 covered the creation of a hierarchical category table, explored two basic queries to list categories and subcategories, and then discussed the "top N" and "top N with ties" queries using the TOP syntax available in Microsoft Access and SQL Server.

Here's the "top N subcategories" query for Oracle:

select cat.id , cat.descr , subcat.id , subcat.descr , subcat.salesamt from categories cat left outer join categories subcat on cat.id = subcat.parentid where cat.parentid is null and subcat.salesamt >= ( select min(salesamt) from ( select distinct salesamt from categories where parentid = cat.id order by salesamt desc ) where rownum <= 3 ) order by cat.id , subcat.salesamt desc

To understand this query, start at the innermost nested query, a correlated subquery which produces a derived table of all distinct sales amounts for all subcategories that have the same parent id. The next subquery, working outwards, selects the smallest value from the top three rows based on ROWNUM. Then the main query selects all subcategories that have a sales amount greater than or equal to that sales amount value. Therefore, ties are included.

Finally, here's the generic "top N subcategories" query, that should work in every database:

select cat.id , cat.descr , subcat.id , subcat.descr , subcat.salesamt from categories cat left outer join categories subcat on cat.id = subcat.parentid where cat.parentid is null and ( select count(*) from categories where parentid = cat.id and salesamt > subcat.salesamt ) < 3 order by cat.id , subcat.salesamt desc

Note that the generic solution may not be as efficient as solutions that involve SQL extensions like TOP or ROWNUM.


This was last published in October 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close