Help finding the top 3 zipcodes within the top 5 counties

[JJA]

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_C ounty 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

[Erland Sommarskog]

JJA (jo***@cbmiweb. com) writes:

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_C ounty 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

This is a whole nicer to do in SQL 2005, where you have ranking functions,
so you can rank the rows in the query.

But now we are on SQL 2000. Being a bit tired tonight, I didn't come up
with anything better than:

SET ROWCOUNT 0 -- don't forget to reset!

CREATE TABLE #temp (ident int IDENTITY,
stateCD ...
countyCD ...
zip ...
Nbr_mtg ...)
INSERT #temp (stateCD, coutnyCD, zip, nbr_mtg)
SELECT D.StateCD, D.CountyCD, D.Zip, 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
GROUP BY D.StateCD, D.CountyCD, D.Zip, Sum(Nbr_MTG) DESC
SELECT a.stateCD, a.countyCD, a.zip, a.nbr_mtg
FROM #temp a
JOIN (SELECT stateCD, countyCD, zip, ident = min(ident)
FROM #temp
GROUP BY stateCD, countyCD, zip= AS b
ON a.stateCD = b.stateCD
AND a.countyCD = b.countyCD
AND a.zip = b.zip
AND a.ident < b.ident +3
ORDER BY a.stateCD, a.countyCD, a.zip, a.nbr_mtg
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[JJA]

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_C ounty 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

[Alexander Kuznetsov]

it would be a snap in 2005, yet it's quite doable in 2000

create table #zips(id int, region char(2), zip int, sum_sales int)
insert into #zips values(1, 'IL', 60563, 12)
insert into #zips values(2, 'IL', 60564, 13)
--- a tie deliberately
insert into #zips values(3, 'IL', 60565, 14)
insert into #zips values(4, 'IL', 60566, 14)
insert into #zips values(5, 'IL', 60567, 14)
insert into #zips values(6, 'IL', 60569, 14)

insert into #zips values(7, 'WI', 53718, 12)
insert into #zips values(8, 'WI', 53711, 1)
insert into #zips values(9, 'WI', 53712, 4)
insert into #zips values(10, 'WI', 53715, 7)
insert into #zips values(11, 'WI', 53714, 5)
insert into #zips values(12, 'WI', 53712, 3)

select * from #zips z
where (select count(*) from #zips z1 where z.region=z1.reg ion
and ((z.sum_sales<z 1.sum_sales)or( z.sum_sales=z1. sum_sales and
z.id<=z1.id))) <= 3

id region zip sum_sales
----------- ------ ----------- -----------
4 IL 60566 14
5 IL 60567 14
6 IL 60569 14
7 WI 53718 12
10 WI 53715 7
11 WI 53714 5

(6 row(s) affected)

drop table #zips

[JJA]

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_C ounty 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