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 |
|
thusi
Starting Member
25 Posts |
 Posted - 2008-09-22 : 22:30:25
|
| Hi AllI have a case where I want rows removed based on an ID, but the row itself is not a duplicate. Sample data:IDM006906 G2.00 Essential hypertension 2004-12-06M006908 G2.00 Hypertensive disease 2003-04-03M007027 G2.00 Hypertensive disease 2005-04-15M007027 G20.00 Essential hypertension 2005-04-15M007075 G2.00 Hypertensive disease 2003-10-01M007267 G2.00 Hypertensive disease 2003-12-17I want the end result to include:M007027 G2.00 Hypertensive disease 2005-04-15ORM007027 G20.00 Essential hypertension 2005-04-15which one doesn't really matter.Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-22 : 23:02:44
|
[code]select *from( select *, row_no = row_number() over (partition by ID order by somecol) from yourtable) dwhere row_no = 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
thusi
Starting Member
25 Posts |
Posted - 2008-09-23 : 06:34:32
|
| wow..THANKS! It works just great!! |
|
|
|
|
|
|
Delete rows based on count(field) > 1/ top 1?