Need help for SQL query

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

EmployeeCode          City
AA1                               XXX
AA1                               NN
BB1                               XXX
BB1                                NN
CC1                                 XXX
DD1                                NN

in the above table - I need to delete employee has more than  one record and delete  only city='NN'
Question by:Varshini S
    LVL 14

    Accepted Solution


    try this one

    :with cte as
    select employeecode,city,row_number () over (partition by employeecode order by city) sn from Table
    Delete from CTE where SN > 1 and city = 'NN' 

    Open in new window


    Author Comment

    by:Varshini S
    Vikas Garg,

    I am sorry. I need to SN=0 not >1 . Am I correct ?

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    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