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
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 first published in October 2003