"username" field) and keep the one with lower ID
ID USERNAME
1 john
2 john2
3 john
after executing this query this should become
ID USERNAME
1 john
2 john2
Thanks,
HowardDo:
DELETE FROM tbl
WHERE id NOT IN ( SELECT MIN( t1.id )
FROM tbl t1
WHERE t1.username = tbl.username ) ;
You can also re-write this using derived table construct or using the
EXISTS() clause.
Anith|||Hi Anith,
I'll be doing the 2005 alternatives today. ;-)
WITH Dups AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Do:
> DELETE FROM tbl
> WHERE id NOT IN ( SELECT MIN( t1.id )
> FROM tbl t1
> WHERE t1.username = tbl.username ) ;
> You can also re-write this using derived table construct or using the
> EXISTS() clause.
> --
> Anith
>|||I should add an ORDER BY clause...
WITH Dups AS
(
SELECT *,
ROW_NUMBER()
OVER(PARTITION BY username ORDER BY id) AS rn
FROM dbo.T1
)
DELETE FROM Dups
WHERE rn > 1;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W
in Israel![url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23vqZQ%23e3FHA.1140@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I'll be doing the 2005 alternatives today. ;-)
> WITH Dups AS
> (
> SELECT *,
> ROW_NUMBER() OVER(PARTITION BY username) AS rn
> FROM dbo.T1
> )
> DELETE FROM Dups
> WHERE rn > 1;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> Join us for the SQL Server 2005 launch at the SQL W
in Israel!> [url]http://www.microsoft.com/israel/sql/sqlw
/default.mspx[/url]>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uAMZ9Rd3FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
