Post reply

Select 10 random zip codes from USA states

  • December 23, 2011 at 3:30 am

    #265349

    There are over 47,000 ZIPCODES in the USA, I want to fetch 10 unique ZIP codes from each states. (Columns are as below) Please help me, much appreciate. Thank you in ADVANCE.

    ----------------------------------tbl_ZIP_USA----------

    STATE_CODE, TOWN, ZIP_CODE, LATITUDE, LONGITUDE

    ------------

    select * from tbl_ZIP_USA

    where zip_code =

    [Make sure that each 50 States are included]

    ----------

    Total rows will be 500 (50 states X 10 zip-codes per state)

  • December 23, 2011 at 3:56 am

    #1425083

    Please in future post the actual table definition. A list of columns can't be copy-pasted to management studio and run.

    This should do what you want, assuming you don't have duplicate rows in the table (you didn't say either way). It'll give you different zip codes every time you run it.

    CREATE TABLE tbl_ZIP_USA (

    STATE_CODE CHAR(2) ,

    TOWN VARCHAR(50) ,

    ZIP_CODE CHAR(5) ,

    LATITUDE NUMERIC(8, 3) ,

    LONGITUDE NUMERIC(8, 3)

    ) ;

    WITH RandomZipCodes

    AS ( SELECT STATE_CODE ,

    TOWN ,

    ZIP_CODE ,

    LATITUDE ,

    LONGITUDE ,

    ROW_NUMBER() OVER ( PARTITION BY STATE_CODE ORDER BY ( SELECT NEWID())) AS rowNo

    FROM tbl_ZIP_USA

    )

    SELECT STATE_CODE ,

    TOWN ,

    ZIP_CODE ,

    LATITUDE ,

    LONGITUDE

    FROM RandomZipCodes

    WHERE rowNo <= 10;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply