Select only rows with unique fields

Question

What is an SQL command that checks rows that contain rows without duplicate fields.

Example:

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

Only lines like A B C D E F

i.e. data:

A A A B B B

A B C D E F

A A B G H Q

Must return A B C D E F

Answer 1

Select distinc *returns unique ROWS values, not unique field values. You must compare each column value with the others. (Assume the column types are the same). For example, for a table with four columns, you should do something 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 2

There is no simple command for this.

, , , , .

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
       )

Answer 3

SELECT DISTINCT * FROM tablename

Answer 4

SELECT DISTINCT col FROM tabl

Answer 5

SELECT * FROM mytable

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