Hi,I currently have this query:SELECT TOP (@intNumberRequired) t2.intItemId,(some calulation stuff) AS fltScore FROM tagMap t4 WHERE t4.intItemId = t2.intItemId)) ) AS fltScore FROM @baseTags t1 JOIN TagMap t2 ON t1.intTagId = t2.intTagId AND t2.intTypeId = @intRecTypeId JOIN @baseItems t3 ON t2.intItemId = t3.intItemId JOIN AttributeMap am ON am.intParentAttributeId = t2.intItemId JOIN Catalogue c ON c.intRowId = am.intChildAttributeId WHERE t2.intItemId <> @intSeedId AND t2.intItemId IN (SELECT [foreign].intItemID FROM TagMap AS [primary] INNER JOIN TagMap AS [foreign] ON [foreign].intTagID = [primary].intTagID WHERE [primary].intItemID = @intSeedid GROUP BY [foreign].intItemID HAVING Count(DISTINCT [foreign].intTagID) >= @intThreshold) GROUP BY t2.intItemId ORDER BY fltScore DESC
Which works. But for efficiency purposes, I'd like to be able to call it so that items which fulfil that HAVING clause in the subquery appear at the top, and items that don't fulfil it appear underneath.It seems I can do this with a CASE statement. So, for example, the subquery could be changed to look like this:SELECT [foreign].intItemID, case when COUNT(distinct [foreign].intTagID) >= @intThreshold then 1 else 0 end as intSubCascade FROM TagMap AS [primary] INNER JOIN TagMap AS [foreign] ON [foreign].intTagID = [primary].intTagID WHERE [primary].intItemID = @intSeedid GROUP BY [foreign].intItemID
And then ordering by intSubCascade at the bottom of the query.However, if you look back at the existing query you'll notice that the subquery supplies values to an IN clause and so only one column can be returned. Is it possible to reconfigure my query to carry on with its basic functionality and also give me the intSubCascade that I need? With a join perhaps, I'm not sure?Cheers,Mattedit: removed extra spaces to fit on screen
Moving HAVING to CASE
Posted - 2012-05-16 : 12:19:54
