Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete duplicate rows SQL

Posted on 2014-03-21
2
Medium Priority
?
396 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 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

609 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