CODEWITHSUNDEEP
sql-servert-sqldatetime
NCollinsTE
NCollinsTE

Reputation: 309

SQL Delete Rows with Duplicate Key Keeping Most Recent

Dearest Professionals,

I have a table that sometimes has rows created with duplicate Invoice #'s (EMP_ID). In these rows, there separate date (FILE_DATE) and time (FILE_TIME) columns (genius database design there). I need to remove the older rows of any duplicated EMP_ID's in this database, keeping the most recent date (from FILE_DATE) + time (from FILE_TIME).

Both FILE_DATE and FILE_TIME are date/time field in the database. The software we use writes to this table, adding the date of the invoice to the FILE_DATE column, with YYYY-MM-DD 00:00:00.000 (the zeros all hard coded). Then the FILE_TIME field has 1900-01-01 HH:mm:ss.SSS, the 1900-01-01 hard coded. (the time stamp comes from the time the row was written to the database)

So, long story short, I need to marry these two together, to get the DATE portion of FILE_DATE and the time portion of FILE_TIME, to get the most recent (IF duplicates exist of EMP_ID) and delete all duplicated that are not the most recent of the married FILE_DATE & FILE_TIME.

Here is a sample of what a Before & After situation would look like.

BEFORE:

enter image description here

AFTER:

enter image description here

Any and all help would be insanely appreciated.

Upvotes: 0

Views: 1599

Answers (2)

StelioK
StelioK

Reputation: 1781

I think this can be accomplished using MAX and GROUP BY:

   select   B.EMP_ID
             , B.File_date
             , Max(B.File_Time) as MaxFileTime
             , B.DESC_TEXT_1
    from     Before B
    group
    by       B.EMP_ID
             , B.File_date
             , B.DESC_TEXT_1

Upvotes: 0

Thom A
Thom A

Reputation: 95557

Using some good old CTE "magic":

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY EMP_ID
                              ORDER BY FILE_DATE DESC, FILE_TIME DESC) AS RN
    FROM YourTable)
DELETE FROM CTE
WHERE RN > 1;

Upvotes: 2

Related Questions