Need help for SQL query

Posted on 2014-08-06
Last Modified: 2014-08-06
I have the following table

State     City      Zipcode
aa            a1        12345
aa            a1          NA

aa            b1        5889      
aa            b1         NA

bb            b1         2245
bb            b1         NA

bb           c1          7788
bb           c1          NA

dd            d1         15678

ee             d1          NA

From the above table I have duplicate records in zipcode (example state aa  and city a1 has a zipcode '12345' but it has another record 'NA' - I want to delete this NA record)

 I have to delete the ZIPCODE='NA'  if only state and city has the  appropriate zipcode.
Do not delete - If  state and city does not have appropriate zipcode and it only has NA


State     City      Zipcode
aa            a1        12345
aa            b1        5889      
bb            b1         2245
bb           c1          7788
dd            d1         15678
ee             d1          NA
Question by:Varshini S
    1 Comment
    LVL 32

    Accepted Solution

    Please post concise and complete examples. Include table DDL and sample data INSERT statements. ASCII arts is - well - kinda annoying..


          [State] CHAR(2) ,
          City CHAR(2) ,
          Zipcode VARCHAR(5)
    INSERT  INTO @Sample
    VALUES  ( 'aa', 'a1', '12345' ),
            ( 'aa', 'a1', 'NA' ),
            ( 'aa', 'b1', '5889' ),
            ( 'aa', 'b1', 'NA' ),
            ( 'bb', 'b1', '2245' ),
            ( 'bb', 'b1', 'NA' ),
            ( 'bb', 'c1', '7788' ),
            ( 'bb', 'c1', 'NA' ),
            ( 'dd', 'd1', '15678' ),
            ( 'ee', 'd1', 'NA' );
    WITH    Ordered
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER ( PARTITION BY [State], City ORDER BY CASE WHEN Zipcode = 'NA' THEN 1
                                                                                         ELSE 0
                                                                                    END ) AS RN
                   FROM     @Sample S
        SELECT  [State] ,
                City ,
        FROM    Ordered
        WHERE   RN = 1;

    Open in new window


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now