How to find top 3 zipcodes in each of the top 5 counties

22 Sep 2005 4:46 PM

JJA

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

22 Sep 2005 5:08 PM

Akbar khan is a Senior Database develope

Hi 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
_____________________________________________________

Show quote

"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
>
>

22 Sep 2005 6:14 PM

JJA

I received an excellent suggestion from Alexander Kuznetsov over at
comp.databases.ms-sqlserver.

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/37238f94a663841f/b499162e4a956b0e?hl=en#b499162e4a956b0e

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