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.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2009-12-04 : 08:11:45
|
| Lets say I have 3 records:OrderID| Product Name | Order Date----------------------------------11111 | Widget A | 9/17/200812323 | Widget A | 8/01/200998765 | Widget A | 10/12/2009Of this list, I want to delete ALL but the most recently ordered record of "Widget A". So in this example, rows 1 & 2 would be deleted, & I would keep the most recent record, row 3.Does anyone know how I could setup a query to do this? Thanks. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-12-04 : 08:32:22
|
| delete from(select *,row_number()over(partition by Productname order by orderdate desc)as rowid from producttable)t where rowid=1PBUH |
 |
|
|
kbhere
Yak Posting Veteran
58 Posts |
Posted - 2009-12-04 : 08:39:28
|
| DELETE FROM TablenameWHERE orderdate NOT IN (SELECT MAX(orderdate) FROM Tablename GROUP BY productname)Balaji.K |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-04 : 08:43:39
|
quote:
Originally posted by Idera
delete from(select *,row_number()over(partition by Productname order by orderdate desc)as rowid from producttable)t where rowid=1PBUH
It should be rowid>1MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-12-04 : 09:11:00
|
quote:
Originally posted by madhivanan
quote:
Originally posted by Idera
delete from(select *,row_number()over(partition by Productname order by orderdate desc)as rowid from producttable)t where rowid=1PBUH
It should be rowid>1MadhivananFailing to plan is Planning to fail
Yeah it shud be that way.Thanks for the catch. Hope the OP did not try my query on his DB or else it wd hd messed up things for him.PBUH |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-04 : 09:20:37
|
quote:
Originally posted by Idera
quote:
Originally posted by madhivanan
quote:
Originally posted by Idera
delete from(select *,row_number()over(partition by Productname order by orderdate desc)as rowid from producttable)t where rowid=1PBUH
It should be rowid>1MadhivananFailing to plan is Planning to fail
Yeah it shud be that way.Thanks for the catch.Hope the OP did not try my query on his DB or else it wd hd messed up things for him.PBUH
Thats why ALL DELETEs and UPDATEs should be tested in TEST server or with Transaction MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
How to delete old duplicate rows?