>>>>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)
>
I instinctively wrote the code correctly; so this is not a problem. Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham