Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Deleting Duplicate Rows

    Hi there,

    I've found multiple solutions to this problem, but not one that I seem to be able to replicate on my database so I'm sorry if this sounds like repetition.

    In my database, I have a table "tbl_c_extract" that consists of 4 columns that look the following. I'm looking at a daily batch of around 4000 records, of which 150 are likely to be duplicates.

    Emp_No varchar(255), Proprietary_ID varchar(255), LeaveDateActual datetime
    123456, E123456, 2014-09-27 00:00:00.000
    213832, E123456, 2099-12-31 00:00:00.000
    213836, E123456, 2014-01-31 00:00:00.000

    In the example above, I need to remove 2 of the entries, leaving only the one that with the maximum leave date. In this case, those without a leave date have the 2099 entry.

    Using CTE works exactly as I want it to, however SQL Server Agent doesn't seem to like the use of CTE. Can someone help me translate this into something functional for SQL Server Agent.


    Code:
    WITH CTE (Proprietary_ID, LeaveDate, RN)
         AS
              (
                   SELECT Proprietary_ID, LeaveDate, 
                   ROW_NUMBER() OVER(PARTITION BY Proprietary_ID ORDER BY Proprietary_ID,   LeaveDate) AS RN
                   FROM tbl_c_extract
              )
                   DELETE
                   FROM CTE
                   WHERE RN > 1

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What makes you think that the SQL Server Agent doesn't like the CTE?

    There shouldn't be any problem using a CTE in SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Ok, disregard my query in total.

    When I first created this step within the job it failed, and it failed a number of times. Since the rest of my argument worked, I assumed the CTE was the cause of failure when it was most likely a typo.

    I've added the code, exactly as I posted above (which was free typed), and it worked.

    Sorry for your trouble.
    TGIF...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •