Purging duplicate records?

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.
szx248Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE TABLE #tempfile ( i int identity , cus varchar(4), item varchar(4) , price smallmoney)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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) )
0
 
szx248Author Commented:
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)
0
 
szx248Author Commented:
awesome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.