CODEWITHSUNDEEP
mysqlsql
Richard Hamilton
Richard Hamilton

Reputation: 26434

mySQL - Selecting all records where all columns are unique

Let's say I have a table like this

id  col1  col2  col3  col4  col5  col6
1    1     2     3     4     5     6
2    2     1     4     3     6     5
3    1     1     2     3     4     5

I would want to select the rows where every field has a different value. The out put would be

id  col1  col2  col3  col4  col5  col6
1    1     2     3     4     5     6
2    2     1     4     3     6     5

I know you can do this

SELECT * FROM table
WHERE col1 <> col2 AND col2 <> col3...

but that would take forever with this many columns. Is there a specific function for determining if all columns are unique?

Upvotes: 1

Views: 75

Answers (3)

Tom H
Tom H

Reputation: 47444

A little less verbose than your solution, although it doesn't scale perfectly if the number of columns is excessively large:

SELECT
    *    -- In actual live code you would of course never use '*'
FROM
    MyTable
WHERE
    col1 NOT IN (col2, col3, col4, col5, col6) AND
    col2 NOT IN (col3, col4, col5, col6) AND
    col3 NOT IN (col4, col5, col6) AND
    col4 NOT IN (col5, col6) AND
    col5 NOT IN (col6)    -- Done as an IN just to be consistent

Upvotes: 2

devpro
devpro

Reputation: 16117

Try this i hope this will helping you

select 
(SELECT group_concat(DISTINCT col1) FROM table) as col1,
(SELECT group_concat(DISTINCT col2) FROM table) as col2,
(SELECT group_concat(DISTINCT col3) FROM table) as col3,
(SELECT group_concat(DISTINCT col4) FROM table) as col4

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can unpivot your table using UNION ALL:

SELECT id
FROM (
  SELECT id, col1 AS col
  FROM mytable

  UNION ALL

  SELECT id, col2
  FROM mytable

  UNION ALL

  SELECT id, col3
  FROM mytable

  UNION ALL

  SELECT id, col4
  FROM mytable

  UNION ALL

  SELECT id, col5
  FROM mytable

  UNION ALL

  SELECT id, col6
  FROM mytable) AS t
GROUP BY id
HAVING COUNT(DISTINCT col) = 6

If you want all columns selected then you do something like:

SELECT *
FROM mytable 
WHERE id IN ( .... above query here ...)

Unfortunately MySQL does not have an UNPIVOT operator that would make the above query less verbose.

Upvotes: 3

Related Questions