>>>Hi,
>>>
>>>I have a table where I want to delete ONE of two duplicate records based on the fact that values in two fields are the same. For example:
>>>
>>>
>>>FIELD1 FIELD2
>>>1 Entry ABC
>>>1 Entry ABC
>>>2 Entry 123
>>>2 Entry 123
>>>
>>>
>>>and so on. There are other fields in this table but they should not be considered when deleting one record. How can you do it in T-SQL?
>>>
>>>TIA.
>>
>>
>>DECLARE @TEst TABLE (FIELD1 int, FIELD2 char(10))
>>INSERT INTO @Test VALUES (1,'Entry ABC')
>>INSERT INTO @Test VALUES (1,'Entry ABC')
>>INSERT INTO @Test VALUES (2,'Entry 123')
>>INSERT INTO @Test VALUES (2,'Entry 123')
>>
>>;with cteTest
>>AS
>>(
>>SELECT *, ROW_NUMBER() OVER (PARTITION BY Field1, Field1 ORDER BY Field1, Field1) AS R_C
>>FROM @TEst Test
>>)
>>
>>DELETE FROM cteTest WHERE R_C > 1
>>
>>SELECT * FROM @TEst
>>
>
>I don't understand the syntax "OVER" and "PARTITION" but I tested your code on my test table and it works. Thank you very much!
I have copy & paste problem in that code :-)
Instead of:
(PARTITION BY Field1, Field1 ORDER BY Field1, Field1)
use
(PARTITION BY Field1, Field2 ORDER BY Field1, Field2)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.