Learn how to a build a cloud-first strategyRegister Now


Purging duplicate records?

Posted on 2006-05-04
Medium Priority
Last Modified: 2006-11-18
I have a table with three fields:

Item        varchar(4)
Customer  varchar(4)
price        smallmoney

sometimes there are more than one matching record. A matching record is when (item + customer) is the same.

Is there a way to eliminate all the duplicates besides for the last one. The last one would be the one that was added to the table last. This table is a temporary table with no indexes.
Question by:szx248
  • 2
  • 2
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16608128
ALTER your temp table to add an identity column 'i'

delete from #tempTable
WHERE i NOT IN (SELECT MAX(i) FROM #tempTable GROUP BY (item + customer) )

Author Comment

ID: 16608187
Very neat!

Now do I give the temporary table an identity column?

this is what my temporary table looks like:
 CREATE TABLE #tempfile ( cus varchar(4), item varchar(4) , price smallmoney)
LVL 75

Accepted Solution

Aneesh Retnakaran earned 200 total points
ID: 16608203
CREATE TABLE #tempfile ( i int identity , cus varchar(4), item varchar(4) , price smallmoney)

Author Comment

ID: 16608324

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 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