TSQL - How do I remove rows that have duplicate columns?

Question

Imagine this table:

| 1 | 2 | 1 | 2 | 1 |
| 1 | 2 | 3 | 2 | 1 |
| 1 | 2 | 3 | 4 | 1 |

How would I go about removing rows with duplicate values, excluding the first and last column...I could very well be thinking of this in an awkward manner. e.g.:

First row : 1 2 1 2 1 - it has two 2s, and 3 1s. I want to remove this because : it has two 2s, and the middle 1 can be found in the beginning, or end column of the row.

Second row : 1 2 3 2 1 - it has two 2s. I want to remove this because : it has two 2s.

Third row : Is fine. the duplicate values at first and last column do not matter, and the values in between are different !

I can imagine a few awkward ways to do this, but since SQL is not my strongest quality, I'd like to hear the pros' opinions :)

Answer 1

DELETE
FROM MyTable
WHERE
  CASE [Col1] WHEN [Col5] THEN 4 ELSE 5 END
  > (SELECT COUNT(DISTINCT v) FROM ( VALUES ([Col1]),([Col2]),([Col3]),([Col4],([Col5]) ) t(v) )

Answer 2

This might look cumbersome, but it does the job:

SELECT *
FROM YourTable
WHERE Col2 NOT IN (Col1,Col3,Col4,Col5)
AND Col3 NOT IN (Col1,Col2,Col4,Col5)
AND Col4 NOT IN (Col1,Col2,Col3,Col5)

Here is a sqlfiddle for you to try.

Answer 3

Another option:

DELETE FROM atable
WHERE EXISTS (
  SELECT 1
  FROM (
    SELECT col1
    UNION
    SELECT col5
    UNION ALL
    SELECT col2
    UNION ALL
    SELECT col3
    UNION ALL
    SELECT col4
  ) AS s (col)
  HAVING COUNT(*) > COUNT(DISTINCT col)
);

For every row, the five columns are combined as rows into a virtual dataset, the col1 and col5 columns being combined with UNION, to eliminate duplication between them, and the other columns being added with UNION ALL. Then the row count in the resulting set is compared with the number of unique values. If those results differ, the given row is to be deleted.