Delete one row from same rows

I have a table T with (first, second) columns. I have two rows with first=1 and second=2. I would like to delete just one of the rows. How do I do th开发者_JAVA百科at?

;WITH CTE AS
(
SELECT TOP 1 *
FROM YourTable 
WHERE first=1 and second=2
)
DELETE FROM CTE;

Or if SQL Server 2000

DELETE T
FROM (
    SELECT TOP 1 *
    FROM YourTable 
    WHERE [first]=1 and [second]=2
) T;

Then add a primary key.

You can use ROW_NUMBER().

DECLARE @T as Table(First int , Second int )

INsert Into @T
Values (1,2),
(1,2)

SELECT * FROM @T

;WITH CTE as 
(SELECT ROW_NUMBER() over (order by first,second) rn , * from @T)

DELETE FROM CTE where rn = 1

select * from @T

If you change rn to include Partition by

ROW_NUMBER() over (PARTITION BY first, second order by first,second)

and change the where to be WHERE RN <> 1

you could use this as a general solution to remove any dupes on First, Second