T-SQL 05 Return 1st record using a duplicate field

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)

T-SQL 05 Return 1st record using a duplicate field

Author

Topic

AnnaCD
Starting Member

1 Post

Posted - 2007-11-15 : 12:15:32

Within a table (Tab1), the field 'A' contains duplicate values, however all other fields in each row are different. I am looking to keep just 1 row (the first after sorting by 'A') for each different occurence of 'A', so cannot use distinct. Could you advise as to how this might be achieved?

Thanks

AnnaDC

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-15 : 12:20:22

What do you mean by first?

set rowcount 1
select 1
while @@rowcount > 0
delete tbl
from tbl t
join (select top 1 a from tbl group by a having count(*) > 1) t2
on t.a = t2.a
set rowcount 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 17:09:21

[code]select *
from
(
select *, row_no = row_number() over (partition by A order by A, B)
from tab1
) a
where row_no = 1[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources