Learn how to a build a cloud-first strategyRegister Now


Need help for SQL query

Posted on 2014-08-06
Medium Priority
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 36

Accepted Solution

ste5an earned 2000 total points
ID: 40244003
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)

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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