Posted by JJA on 10/01/27 11:27

Thanks very much for your suggestion. I couldn't get it to work as it
was but I tried to take the idea and apply it. Came up with a new
version (same objective) but I am still stuck. I cannot get it to peel
off the top 3 rows in each county. I get 393 rows in the final
resultset where I really want only 15 rows (5 counties times top 3
zipcodes in each county). I am beginning to think I need a cursor.
Here's my SQL:

SET ROWCOUNT 5
DECLARE @tblTemp TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
Zip CHAR(5),
Nbr_Mtg INT)
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

SET ROWCOUNT 0
INSERT INTO @tblTemp (StateCD, CountyCD, Zip, Nbr_Mtg)
SELECT 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' AND D.NonPro IS NOT NULL
GROUP BY D.StateCD, D.CountyCD, D.Zip
Order By Sum(Nbr_MTG) DESC
DECLARE @tblByCounty TABLE (
ident int IDENTITY,
StateCD CHAR(2),
CountyCD CHAR(3),
Zip CHAR(5),
Nbr_Mtg INT,
NationalRank INT)
INSERT INTO @tblByCounty (StateCD, CountyCD, Zip, Nbr_Mtg,
NationalRank)
SELECT A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.ident AS NationalRank
FROM @tblTemp A -- this set ranks by biggest zipcodes WITHIN
each county
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip
SELECT A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.ident, A.NationalRank
FROM @tblByCounty A -- this set ranks by biggest zipcodes WITHIN
each county
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip

SELECT A.ident, B.ident, A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg,
A.NationalRank
FROM @tblByCounty A
JOIN
(SELECT Min(X.ident) AS ident, X.StateCD, X.CountyCD, X.Zip,
X.Nbr_Mtg, X.NationalRank
FROM @tblByCounty X
GROUP BY X.StateCD, X.CountyCD, X.Zip, X.Nbr_Mtg, X.NationalRank
) AS B
ON A.StateCD = B.StateCD
AND A.CountyCD = B.CountyCD
AND A.Zip = B.Zip
AND A.ident = B.ident
WHERE A.ident < B.ident + 3
ORDER BY A.StateCD, A.CountyCD, A.Nbr_Mtg DESC

[Back to original message]