Avatar of olivejooce
olivejooce asked on

How do I remove duplicate SQL records, leaving only one by most recent date?

I need to create a SQL View that removes duplicate records. Right now when I run the query, it is pulling in all records and I want it to only pull up the most recently updated record.

The SQL View name is:  FM_Report and it is pulling info from two Tables: FM_Project and Project_Status_History

-- I want to remove all duplicate records sharing the same: SBBC_Project_Number (found in both tables)
-- I want to leave just the most recent record by the most recent update: Project_Status_Date (found in the Project_Status_History table)

Can you suggest a script for this, please?
Microsoft SQL Server

Avatar of undefined
Last Comment
Sharath S

8/22/2022 - Mon
Sharath S

What is your SQL  version?
ASKER CERTIFIED SOLUTION
reb73

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sharath S


>> I need to create a SQL View that removes duplicate records.
View can not delete your records. you can create an SP for that. 
Do you want to permanently delete the duplicate records from your both tables or you just want to display the distinct records?
 
For hard deltes from your tables, you can use this.
 
 DELETE T FROM (
 SELECT row_number() OVER(ORDER BY SBBC_Project_Number,YourDateColumn DESC) as row_number, * FROM FM_Project ) T
 WHERE row_number not in (
 SELECT min(row_number) FROM (SELECT row_number() OVER(ORDER BY SBBC_Project_Number,YourDateColumn DESC) as row_number, * FROM FM_Project) T 
 GROUP BY SBBC_Project_Number )
 -- check whether the records are deleted.
 SELECT * FROM FM_Project
 
 
-- similarly for Project_Status_History table also you can write the code.
  DELETE T FROM (
 SELECT row_number() OVER(ORDER BY SBBC_Project_Number,YourDateColumn DESC) as row_number, * FROM Project_Status_History ) T
 WHERE row_number not in (
 SELECT min(row_number) FROM (SELECT row_number() OVER(ORDER BY SBBC_Project_Number,YourDateColumn DESC) as row_number, * FROM Project_Status_History) T 
 GROUP BY SBBC_Project_Number )
 
 

Open in new window

ASKER
olivejooce

I tried it and it didn't work.  I'm using SQL Server 2000 (ancient, I know).  I'm new to all this so it's probably me.  I'm attaching a snapshot of the SQL View, perhaps it'll help.  Thank you!
sh-01.gif
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sharath S


Question - Do you want to hard delete the records from your tables? Or do you want your view to display only those records which are recently updated?
If you just want to display through View, you need not to delete the records in your tables?
ASKER
olivejooce

Hi Sharath,
No, I don't want to delete the records.  I just want the view to display only one project record(SBBC_Project_Number) that was most recently updated (Project_Status_Date).

Thanks for your help!!!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.