Top 10 unless count is zero
- by datatoo
This is probably easy, but eludes me.
SQL server2005
I want to show top 100 but if there are not 100 only want to show those and not include zero counts in the result
SELECT TOP (100) UserName, FullName_Company, FullName,
(SELECT COUNT(*)
FROM dbo.Member_Ref
WHERE (RefFrom_UserName = dbo.view_Members.UserName) AND (RefDate >= '5/1/2010') AND (RefDate <= '6/1/2010'))
AS RefFromCount
FROM dbo.view_Members
WHERE (MemberStatus = N'Active')
ORDER BY RefFromCount DESC
I have tried using Group By and HAVING COUNT(*)0 all with the same wrong results
