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.
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 )
 
 

Select allOpen in new window