Posted by JJA on 09/21/05 21:05

I need to show data for the top 3 zipcodes for EACH of the top 5
counties. I feel totally blocked on how to make this work properly.
Here is my code - anything you can suggest will be greatly appreciated:

SET ROWCOUNT 5
DECLARE @tblTopMarkets TABLE (StateCD CHAR(2), CountyCD CHAR(3))
INSERT INTO @tblTopMarkets
select
S.StateCD,
S.CountyCD
FROM DAPSummary_By_County S
WHERE S.SaleMnYear > '01/01/2004'
GROUP BY S.StateCD, S.CountyCD Order By Sum(S.Nbr_MTG) DESC
-- the above works fine but next select produces only 3 rows;
-- I need 3 times 5 rows (how to effect a "loop")
SELECT TOP 3 -- zips in a county
D.StateCD,
D.CountyCD,
D.Zip,
"Nbr_Mtg" = Sum(Nbr_MTG)
FROM @tblTopMarkets T
LEFT JOIN GovtFHADetails D
ON T.StateCD = D.StateCD AND T.CountyCD = D.CountyCD

WHERE D.SaleMnYear > '01/01/2004'
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESC

[Back to original message]