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) DESCJJA (johna@.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_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
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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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),
ZipCHAR(5),
Nbr_MtgINT)
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),
ZipCHAR(5),
Nbr_MtgINT,
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|||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.region
and ((z.sum_sales<z1.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|||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),
ZipCHAR(5),
Nbr_MtgINT)
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),
ZipCHAR(5),
Nbr_MtgINT,
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
