Select only rows that have unique fields

Question

What is an SQL command that checks for rows that have rows with no duplicate fields in them.

ex:

A A A B B B should not be in the resulting table.

Only rows such as A B C D E F

i.e. given data like:

A A A B B B

A B C D E F

A A B G H Q

Should return A B C D E F

Answer 1

SELECT DISTINCT * FROM tablename

Answer 2

SELECT DISTINCT col FROM tabl

Answer 3

SELECT * FROM mytable

WHERE mytable.col1 != mytable.col2 != mytable.col3 ...

Answer 4

Select distinc * returns unique ROWS not unique values from fields. You should compare each column's value with others. (Assuming column types are the same). For example, for a 4 column table you should do smoething like:

  SELECT Col1, Col2, Col3, Col4 FROM MyTable WHERE
  Col1 NOT IN (Col2,Col3,Col4) AND
  Col2 NOT IN (Col3,Col4) AND
  Col3 <> Col4

Answer 5

There is no simple command to do this.

is seems an unusual requirement and possibly an indication that the table is not in first normal form if all columns are interchangeable.

The following works in Microsoft SQL Server

;With YourData AS
(
select 'A' as C1, 'A' as C2, 'A' as C3, 'B' as C4,  'B' as C5,  'B' as C6 UNION ALL
select 'A' as C1, 'B' as C2, 'C' as C3, 'D' as C4,  'E' as C5,  'F' as C6
)
SELECT *
FROM   YourData
WHERE  1 =
       ( SELECT  TOP 1 COUNT(*) AS Cnt
       FROM     ( 
                SELECT C1 AS     C
                UNION ALL
                SELECT C2
                UNION ALL
                SELECT C3
                UNION ALL
                SELECT C4
                UNION ALL
                SELECT C5
                UNION ALL
                SELECT C6
                ) D
       GROUP BY C
       ORDER BY Cnt DESC
       )