Solved

Delete duplicate rows SQL

Posted on 2014-03-21
2
379 Views
Last Modified: 2014-03-21
I have a table with the following columns,
ProductID, BusDate, Code, LastUpdatedDate

I need to delete duplicate rows and just keep a row with the highest timestamp

I tried this query to give me the timestamp that I want to keep and delete the rows that have the same
ProductID, BusDate, Code but a LastUpdatedDate not equal to the max(LastUpdatedDate)

select ProductID, BusDate, Code, MAX(LastUpdatedDate), COUNT(*)
FROM Products
GROUP BY  ProductID, BusDate, Code
HAVING COUNT(*) > 1

3097      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4
3097      2014-03-01 00:00:00.000      INTERNET      2014-03-11 09:24:06.983            4
3099      2014-03-01 00:00:00.000      COMMEQP            2014-03-11 09:24:06.983            4
3099      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4
3115      2014-03-01 00:00:00.000      BANKS            2014-03-11 09:24:06.983            3
3115      2014-03-01 00:00:00.000      COUNTRY            2014-03-11 09:24:06.983            4
0
Comment
Question by:countrymeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
jayakrishnabh earned 500 total points
ID: 39945256
;WITH CTE AS(
   SELECT ProductID, BusDate, Code, LastUpdateDate,
       RN = ROW_NUMBER()OVER(PARTITION BY ProductID, BusDate, Code ORDER BY LastUpdateDate Desc)
   FROM dbo.Table_2
)
DELETE FROM CTE WHERE RN > 1
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39945257
You could probably use something similar to:

with duplicateRows as
(
	select
		ProductID,
		ROW_NUMBER( ) over( partition by productID, busdate, code order by lastUpdatedDate desc ) as rowNum
	from
		Products
)

delete duplicateRows
	where rowNum > 1;

Open in new window

0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL / Table Lock? 7 38
Server 2012 r2 and SQL 2014 6 32
Error "Could not load mobile report" in SQL SSRS 2016 after SP1 CU2 Update 2 33
Search Text in Views 2 24
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question