CODEWITHSUNDEEP
sqlsas
DIY-DS
DIY-DS

Reputation: 263

only select the rows with different value

I want to select two rows with different value

Obs  x   y   z
1   23  49  16
2   23  16  83
3   49  16  83
4   23  49  83
5   23  83  75
6   49  83  75
7   16  83  75
8   23  16  75
9   49  16  75
10  23  49  75
11  23  75  42
12  49  75  42
13  16  75  42
14  83  75  42
15  23  83  42
16  49  83  42
17  16  83  42
18  23  16  42
19  49  16  42
20  23  49  42

The Table name is called MyData, it is a 20*3 table. If you watch the rows carefully, you will always find two rows with distinct value...I want them both out and make a new 10*6 table.

For instance

23 49 16 should combine with 83 75 42

23 16 83 should combine with 49 75 42

...

Final table should look like:( 10*6 table)

a b c d e f

23 49 16 83 75 42

23 16 83 49 75 42 ...

Upvotes: 0

Views: 61

Answers (1)

wvdz
wvdz

Reputation: 16641

If you mean a selfjoin with all rows that have different values for all columns:

SELECT *
FROM tab t1
JOIN tab t2
ON t1.x NOT IN (t2.x,t2.y,t2.z)
AND t1.y NOT IN (t2.x,t2.y,t2.z)
AND t1.z NOT IN (t2.x,t2.y,t2.z)

Upvotes: 1

Related Questions