I am having trouble finding the solution to the above for SQL Server 2005. I only have to use two tables. What I have to do is return the top 5 Items -- top 5 meaning the count of how many records for each distinct Item and return just the top 5 counts (from table T1). Next, for each of the top 5 Items I need to return the top 5 (record counts) which consist of the unique combination values of three columns: Category, Subcategory, Product_Type (from table T2 (T1.ID = T2.ID)). My results should look something like: 1) Hardware - 1. complaint- service delivery - performance 2. incident - failure - error message 3. incident - failure - job failed 4. ... 5. ... 2) Application - 1. complaint - service delivery - availability 2. complaint - service delivery - functionality 3...., etc I have tried using ROW_NUMBER( ) but I am not getting the top 5 counts. Any help would be greatly appreciated!
Try writing the query exactly as you describe it. Seems like you're starting off with the selection criteria, so the first part of your description is the WHERE clause - you're probably trying to do this in the SELECT part, and that's where you get stuck.
You can modify this accordingly http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx