Q

Top N subcategories

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.

Thanks for making part of this problem easy. Ordinarily, one would not expect to find "associated elements" in the category table. For example, product sales would be in a sales table, which would be joined to a product master table, which would in turn be joined to the category table, and then there would be a GROUP BY on the category to get SUM(productsales). However, if these joins are "pre-defined" into a view, then we can simply use the view in the same way that we're going to use the sample category table defined below, which has the sales data incorporated as one of the "associated elements". It makes the query that much easier.

If you're comfortable with a self-referencing hierarchical table, you can skip right to Part 3 of this answer. The remainder of Parts 1 and 2 will be devoted to building up the category hierarchy, for those who are new to the structure.

We'll start by populating the category table with the "top level" or major categories:

create 
 table categories
     ( id        smallint identity not null 
     , parentid  smallint              null
     , descr     varchar(15)       not null
     , salesamt  integer               null  
     , constraint categories_pk
           primary key ( id )
     , constraint parent_category_fk
           foreign key ( parentid )
           references categories ( id )
     )
insert 
  into categories ( descr )
           values ('Category A') 
insert 
  into categories ( descr )
           values ('Category B') 
insert 
  into categories ( descr )
           values ('Category C') 
insert 
  into categories ( descr )
           values ('Category D')

Now let's run a SELECT to see what we've got so far:

select * from categories           

id parentid descr salesamt ------ -------- --------------- ----------- 1 NULL Category A NULL 2 NULL Category B NULL 3 NULL Category C NULL 4 NULL Category D NULL

Notice that the parentid is null; this is what identifies categories at the top level. (They don't have sales amounts, either; they could, but that would make the query messier.)

Now let's add a subcategory:

insert 
  into categories ( descr, parentid, salesamt )
           values ( 'subcat A1', 1, 103 )

Notice that the parentid value for this subcategory is given as 1, which corresponds to Category A. In other words, we need to know the value of the parent category's id before we can insert a subcategory under that category.

Now let's assume we've added several additional subcategories in this manner, and run another SELECT to see the categories and their subcategories:

id     parentid descr           salesamt    
------ -------- --------------- ----------- 
1      NULL     Category A      NULL
2      NULL     Category B      NULL
3      NULL     Category C      NULL
4      NULL     Category D      NULL
5      1        subcat A1       103
6      1        subcat A2       207
7      1        subcat A3       94
8      1        subcat A4       255
9      1        subcat A5       159
10     4        subcat D1       150
11     4        subcat D2       195
12     2        subcat B5       315
13     2        subcat B4       301
14     2        subcat B3       377
15     2        subcat B2       315
16     2        subcat B1       315

Continued in Part 2.

This was first published in October 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close