remove duplicates but leave the latest added

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

SQL Server 2005 Forums

Transact-SQL (2005)

remove duplicates but leave the latest added

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-13 : 17:17:23

Hi

I have a problem that I really need help with. I have a table that looks like this..

tbl_Order

ID = ID Not null
NodeID = Int
Qty = Int
CartID = nVarchar

This table contains duplicate of the NodeID value like this..

ID NodeID Qty CartID

1 11 1 11-22
2 11 1 11-22
3 11 2 11-22
4 34 10 11-22
5 34 10 11-22
6 11 6 16-27
7 34 13 16-27
8 34 20 16-27

Is there any way I can delete all obsolete NodeID rows so that only the latest added row (the one with the highest ID value) is left based on the CartID. I would like the final table to look like this...

3 11 2 11-22
5 34 10 11-22
6 11 6 16-27
8 34 20 16-27

Best Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 17:19:28

[code]DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY ID DESC) AS recID
) AS f
WHERE recID > 1[/code]

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-13 : 17:27:40

That was fast.... but don't I need to pass on the table name? Also, is there a way to check what rows it will delete? I would like to check that it does not delete any rows that should be there.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 17:49:14

[code]DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY ID DESC) AS recID
FROM YourTableNameHere
) AS f
WHERE recID > 1[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 17:50:17

For checking, replace "DELETE f" with "SELECT *"

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-14 : 00:07:50

Peso, I get this error ..
Invalid column name 'f'.

in this query..

SELECT f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY ID DESC) AS recID
FROM tbl_Order
) AS f
WHERE recID > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 00:13:28

quote:
Originally posted by magmo

Peso, I get this error ..
Invalid column name 'f'.

in this query..

SELECT f *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY NodeID ORDER BY ID DESC) AS recID
FROM tbl_Order
) AS f
WHERE recID > 1

for select replace f with * which is what Peso has suggested you

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-14 : 02:17:22

will this query return the duplicates for each CartID? I mean there can be one CartID (nvarchar) that have 3 duplicate items, then there can be another CartID that have the same items but only 1 duplicate. I wonder beacuse the CartID column isn't mentioned in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:52:14

quote:
Originally posted by magmo

will this query return the duplicates for each CartID? I mean there can be one CartID (nvarchar) that have 3 duplicate items, then there can be another CartID that have the same items but only 1 duplicate. I wonder beacuse the CartID column isn't mentioned in the query.

so you're looking at duplicates based on cartid value. then query should be

SELECT f *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CartID ORDER BY ID DESC) AS recID
FROM tbl_Order
) AS f
WHERE recID > 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 02:57:47

In your original post, your wanted result is based on both CartID and NodeID.

SELECT	*
--DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY CartID, NodeID ORDER BY ID DESC) AS recID
		FROM	tbl_Order
	) AS f
WHERE	recID > 1

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-14 : 03:01:00

I'm not sure that is correct, I think I get to many results when I run that query.

I will try to explain the situation.

tbl_Order hanve columns like this..

ID (int, counter, pk)
NodeID (int)
Qty (int)
CartID (nvarchar)

And it have values like this..

ID NodeID Qty CartID

1 11 2 123-456
2 11 2 123-456
3 11 2 123-456
4 22 1 123-456
5 22 1 567-899
6 22 1 567-899

Here I want to remove the following rows..
1 11 2 123-456
2 11 2 123-456
5 22 1 567-899

And the following rows should be left in tbl_Order..
3 11 2 123-456
6 22 1 567-899

Beacuse they are unique and they have the highest ID value.

Hope this make sence

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 03:05:09

[code]SELECT * -- Displays the records that later will be deleted
--DELETE f -- Delete the records considered to be duplicates
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CartID ORDER BY ID DESC) AS recID
FROM tbl_Order
) AS f
WHERE recID > 1[/code]Visakh posted this earlier.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 03:10:56

what will happen to record with ID 4?

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-14 : 03:24:35

Sorry, forgot that one. That row should not be deleted

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 03:26:30

Then you need to partition by BOTH CartID and NodeID as my previous post said.

SELECT	* -- Displays the records that later will be deleted
--DELETE	f -- Delete the records considered to be duplicates
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY CartID, NodeID ORDER BY ID DESC) AS recID
		FROM	tbl_Order
	) AS f
WHERE	recID > 1

E 12°55'05.63"
N 56°04'39.26"

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-01-14 : 03:36:22

Thank you both for all help, it worked just the way I wanted it to.

Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources