delete query - SQL Server Forums

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)

delete query

Author

Topic

clu82
Starting Member

4 Posts

Posted - 2009-05-06 : 10:57:12

I have this query to count the number of duplicate pricing rows (returns 430 rows):

select count(price_class), count(item_number) from pricing
group by price_class, persistent_item_number
having (count(price_class) > 1)

I need to be able to delete these duplicate rows. Any ideas?

tia,
clu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-06 : 11:17:29

run this and see if this is want you wanted ?


select price_class, persistent_item_number, count(price_class), count(item_number) 
from   pricing
group by price_class, persistent_item_number
having (count(price_class) > 1)

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-06 : 11:18:18

try


Delete a
from 
(Select row_number() over (Partition by Persistant_Item_Number,Price_Class order by Price_Class) as RowID,*
from
Pricing 
) a
where a.RowID > 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

clu82
Starting Member

4 Posts

Posted - 2009-05-06 : 14:13:21

quote:

Originally posted by Vinnie881

try


Delete a
from 
(Select row_number() over (Partition by Persistant_Item_Number,Price_Class order by Price_Class) as RowID,*
from
Pricing 
) a
where a.RowID > 1

Vinnie, worked great. Thank you. Do you have an article that explains this (for a simpleton like me)?

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources