Using SQL Server 2000, I am trying to produce a showing the top 3
zipcodes in each of the top 5 counties. I have tried lots of variations
and I am really stuck. 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 DESCHi there
It seems that the ZIp number is string in type but it is a number in nature.
I encounter that if you have format like 1.2.3.4.5.6 or 123-42134-2342-3
like this then the sql server is unable to order that properly.
You can solve this by terminating the [.] or [-] with [0] so the column will
be sorted properly.
If it is helpfull and you want more help then let me know the zip code
format.
Thanks
________________________________________
_____________
"JJA" wrote:
> Using SQL Server 2000, I am trying to produce a showing the top 3
> zipcodes in each of the top 5 counties. I have tried lots of variations
> and I am really stuck. 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
>|||I received an excellent suggestion from Alexander Kuznetsov over at
comp.databases.ms-sqlserver.
http://groups.google.com/group/comp...499162e4a956b0e
This works beautifully. Here is my final adaptation of his idea:
(One key to this is the SELECT COUNT(*) near the bottom of the post)
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
0x5CD00098)