Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: