How 2 remov duplicate row & keep the last updated? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How 2 remov duplicate row & keep the last updated?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-02-19 : 05:18:24

Hi

I have rows are like below. I am uploading data every day from csv, but some of the rows are getting duplicated and ending up having more than 6 rows for 1 product.
(ex)
ID(AutoNumber)---code(Unique)-----Col2--------Col3--------Col4
23--------------YUW345-12 ------1-----------dfgg-----------AB
24--------------YUW345-12 ------1-----------dfgg-----------AB
25--------------YUW345-12 ------4-----------dfgg-----------AB

From the above example I need to delete two rows which is ID less than last ID for a code (YUW345-12). So I can have single row for each product. I have nearly 6000 rows including the duplicates which of them I need only 3400.
I am using below query to see which codes have more than one record, So colud anyone please tell me how to do the above said in one query?

SELECT     code, COUNT(code) AS CNT
FROM         tblProducts
GROUP BY code
ORDER BY cnt DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 05:25:54

IF its sql 2005 use:-

DELETE t FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY ID DESC) AS RowNo,
code,
Col2,
Col3,
Col4
FROM Table
)t
WHERE t.RowNo<>1

If 2000:-

DELETE t FROM
(
SELECT (SELECT COUNT(*) + 1 FROM Table WHERE code=t.code AND ID > t.ID) AS RowNo,
code,
Col2,
Col3,
Col4
FROM Table t
)t
WHERE t.RowNo<>1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 05:28:48

[code]Delete t1
from table t1
join
(select code from table group by code having count(*) > 1) t2 on t1.code=t2.code
where id < (select max(id) from table where code = t2.code)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 05:46:53

[code]DELETE x
FROM tblProducts AS x
LEFT JOIN (
SELECT MAX(ID) AS ID
FROM tblProducts
GROUP BY Code
) AS y ON y.ID = x.ID
WHERE y.ID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 07:22:40

If it is SQL Server 2000,I prefer joining with Min or Max of column than relying on serial no

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2008-02-19 : 09:23:33

Hi
Thanks a lot guys.It works

quote:

Originally posted by Peso

DELETE		x
FROM		tblProducts AS x
LEFT JOIN	(
			SELECT		MAX(ID) AS ID
			FROM		tblProducts
			GROUP BY	Code
		) AS y ON y.ID = x.ID
WHERE		y.ID IS NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:30:12

quote:

Originally posted by harsh_athalye

Delete t1
from table t1
join
(select code from table group by code having count(*) > 1) t2 on t1.code=t2.code
where id < (select max(id) from table where code = t2.code)

An equivalent

DELETE		x
FROM		tblProducts AS x
INNER JOIN	(
			SELECT		Code,
					MAX(ID) AS ID
			FROM		tblProducts
			GROUP BY	Code
			HAVING		COUNT(*) > 1
		) AS y ON y.Code = x.Code
WHERE		x.ID < y.ID

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 09:34:04

Peter,

I liked your first solution, much more compact and efficient.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources