Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003

    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.

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

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    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.

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

  3. #3
    Join Date
    Oct 2003
    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.

Posting Permissions

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