Top 10 unless count is zero

Question

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

Answer 1

Consider:

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')) 
                        HAVING COUNT(*) > 0
                  AS RefFromCount           
FROM         dbo.view_Members
WHERE (MemberStatus = N'Active')
ORDER BY RefFromCount DESC

The added HAVING clause on the inner query will inherently cut out results with 0 counts.

Answer 2

I join to a subquery with quantities and in the where part I filter by quantity > 0.

SELECT TOP (100) UserName, FullName_Company, FullName, Quantity AS RefFromCount          
FROM         dbo.view_Members
JOIN (
    SELECT RefFrom_UserName, COUNT(*) as Quantity
    FROM          dbo.Member_Ref
    WHERE      (RefDate >= '5/1/2010') AND (RefDate <= '6/1/2010')
    GROUP BY RefFrom_UserName
) as Q on Q.RefFrom_UserName = dbo.view_Members.UserName
WHERE (MemberStatus = N'Active')
AND Quantity > 0
ORDER BY RefFromCount DESC

Answer 3

Does SQL Server 2005 have keyword like "limit" in MySQL? (If so, there is a very simple solution for this kind of task)