|
|
Posted by Erland Sommarskog on 09/22/05 00:48
JJA (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/techinfo/productdoc/2000/books.asp
[Back to original message]
|