troubleshooting Question

Select Top X usng two level sub queries in where clause

Avatar of dij8
dij8Flag for New Zealand asked on
Microsoft SQL Server
6 Comments1 Solution358 ViewsLast Modified:
This question is a carry on from https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22045889.html

I am trying to get the top X (in the sample below, 5) values where values are first grouped by a set value.  I'll try to explain clearly.

I have a table with a Category ID and a Sub-Category ID.  I want the top X items from each sub-category within every category where the sub-category items are in a specified order.  It is highly likely the sub-categories will be repeated within each category.  As in Category 1 has sub-categories a, b, c, d, and e and category 2 has sub-categories b, c, d, e, and f.

My SQL is dynamic so there may be more involved here than you first think.  We'll figure that out as the possible answers come pouring in. :-)

The SQL I currently have (as generated) returns the top X items for each sub-category but only for the first category it encounters.  I need it to be repeated for each category as well.  I have tried swapping the WHERE IN clauses around but get the same result either way.

SELECT ModuleHTMLContent.ModuleHTMLContentId
      , (SELECT TOP 1 LookUpValue.[Name]
            FROM LookUpValue
            INNER JOIN LookupGroupValue tCLGV ON tCLGV.LookupValueId = LookupValue.LookupValueId
            WHERE LookupValue.LookupValueId = ModuleHTMLContent.CategoryTypeId
            AND (tCLGV.SiteId = 1 OR tCLGV.SiteId = 0)) AS CatName
      , (SELECT TOP 1 tCLGV.OrderNumber
            FROM LookUpValue
            INNER JOIN LookupGroupValue tCLGV ON tCLGV.LookupValueId = LookupValue.LookupValueId
            WHERE LookupValue.LookupValueId = ModuleHTMLContent.CategoryTypeId
            AND (tCLGV.SiteId = 1 OR tCLGV.SiteId = 0)) AS CatOrder
      , (SELECT TOP 1 [Name]
            FROM LookUpValue
            INNER JOIN LookupGroupValue tCLGV ON tCLGV.LookupValueId = LookupValue.LookupValueId
            WHERE LookupValue.LookupValueId = ModuleHTMLContent.SubCategoryTypeId
            AND (tCLGV.SiteId = 1 OR tCLGV.SiteId = 0)) AS SubCatName
      , (SELECT TOP 1 tCLGV.OrderNumber
            FROM LookUpValue
            INNER JOIN LookupGroupValue tCLGV ON tCLGV.LookupValueId = LookupValue.LookupValueId
            WHERE LookupValue.LookupValueId = ModuleHTMLContent.SubCategoryTypeId
            AND (tCLGV.SiteId = 1 OR tCLGV.SiteId = 0)) AS SubCatOrder
      , (SELECT COUNT(*) FROM PageHit
            RIGHT JOIN PageContainer gPC ON PageHit.PageId = gPC.PageId
            RIGHT JOIN PageContainerModule gPCM ON gPC.PageContainerId = gPCM.PageContainerId
            RIGHT JOIN ModuleHTMLContentItem gMHCI ON gPCM.PageContainerModuleId = gMHCI.PageContainerModuleId
            WHERE gMHCI.HTMLContentId = ModuleHTMLContent.ModuleHTMLContentId) AS Hits
      , ModuleHTMLContent.Author AS Author
      , ModuleHTMLContent.CreatedDateTime AS Created
      , ModuleHTMLContent.ItemDate AS ListDate
      , ModuleHTMLContent.UpdatedDateTime AS Updated
      , ModuleHTMLContent.Title AS Title
FROM ModuleHTMLContent
WHERE      (ModuleHTMLContent.SiteId = 1)
      AND ModuleHTMLContent.IsOnHold<>1
      AND ModuleHTMLContent.CategoryTypeId > 0
      AND ModuleHTMLContent.ModuleHTMLContentId IN
            (SELECT TOP 5 a3.ModuleHTMLContentId
            FROM ModuleHTMLContent a3, LookupValue xSLV, LookupGroupValue xtSLGV, LookupGroup xSLG
            WHERE a3.SubCategoryTypeId = ModuleHTMLContent.SubCategoryTypeId
                  AND xSLV.LookupValueId = ModuleHTMLContent.SubCategoryTypeId
                  AND xtSLGV.LookupValueId = xSLV.LookupValueId
                  AND xSLG.LookupGroupId = xtSLGV.LookupGroupId
                  AND ModuleHTMLContent.ModuleHTMLContentId IN
                        (SELECT TOP 10000 a2.ModuleHTMLContentId
                        FROM ModuleHTMLContent a2, LookupValue xCLV, LookupGroupValue xtCLGV, LookupGroup xCLG
                        WHERE a2.CategoryTypeId = ModuleHTMLContent.CategoryTypeId
                              AND xCLV.LookupValueId = a2.CategoryTypeId
                              AND xtCLGV.LookupValueId = xCLV.LookupValueId
                              AND xCLG.LookupGroupId = xtCLGV.LookupGroupId
                        ORDER BY  xCLV.[Name] ASC)
            ORDER BY xSLV.[Name] ASC)
ORDER BY  CatName ASC, SubCatName ASC , ListDate  DESC
ASKER CERTIFIED SOLUTION
Brendt Hess
Senior DBA

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros