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

You are brilliant! Thank you so much for your help! I adapted your
approach and example to my data and it works beautifully. Here is my
final 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
ORDER BY A.StateCD, A.CountyCD, A.Nbr_MTG DESC, A.Zip

SELECT A.ident, A.StateCD, A.CountyCD, A.Zip, A.Nbr_Mtg, A.NationalRank
FROM @tblByCounty A
WHERE
(SELECT COUNT(*)
FROM @tblByCounty X
WHERE X.StateCD = A.StateCD AND X.CountyCD = A.CountyCD
AND
(
(A.Nbr_Mtg < X.Nbr_Mtg)
OR
( A.Nbr_Mtg = X.Nbr_Mtg AND A.ident <= X.ident)
)
) <= 3
ORDER BY A.StateCD, A.CountyCD, A.Nbr_Mtg DESC

[Back to original message]