SQL select distinct but exhaustive columns

Question

I have a simple table with 2 columns:

Col1 | Col2
-----+-----
1    | 3
1    | 4
2    | 3
2    | 4
... many more rows

I want to return this:

Col1 | Col2
-----+-----
1    | 3
2    | 4

I don't want this:

Col1 | Col2
-----+-----
1    | 3
2    | 3

because 3 is duplicated in Col2, nor this

Col1 | Col2
-----+-----
1    | 3
1    | 4

because 1 is duplicated in Col1, nor this

Col1 | Col2
-----+----
1    | 3

because now 4 is missing in Col2. In other words, I don't want duplicates, but I also don't want to omit any values in col2 (unless it occurs with a duplicate in Col1 - and vice versa). How can I use SQL to do what I want? Thanks.

Please look at this: http://stackoverflow.com/questions/12188027/mysql-select-distinct-multiple-columns — Ernestas Stankevičius, Mar 02 '16 at 08:52
What should happen when `col1` has `1,1,2` and `col2` has `3,4,5` — Pரதீப், Mar 02 '16 at 09:20

score 0 · Answer 1 · answered Mar 02 '16 at 09:44

0

its working as per your given sample data

select col1,
case
when col1 =1 and col2 in (3,4) Then 3
when col1 =2 and col2 in (3,4) Then 4
end as col2
from table1
group by col1

for more detail go here sql fiddle

answered Mar 02 '16 at 09:44

Ankit Agrawal

2,426
1
13
27

shA.t · Answer 2 · 2016-03-02T11:27:43.257

I think you need a query like this:

SELECT Col1, Col2
FROM 
    (SELECT dt.*, @rownum1 := @rownum1 + 1 AS rn
     FROM 
        (SELECT Col1
         FROM t
         GROUP BY Col1) dt,
        (SELECT @rownum1 := 0) r) t1
    LEFT OUTER JOIN
    (SELECT dt.*, @rownum2 := @rownum2 + 1 AS rn
     FROM 
        (SELECT Col2
         FROM t
         GROUP BY Col2) dt,
        (SELECT @rownum2 := 0) r) t2
    ON t1.rn = t2.rn
UNION ALL
SELECT Col1, Col2
FROM 
    (SELECT dt.*, @rownum3 := @rownum3 + 1 AS rn
     FROM 
        (SELECT Col2
         FROM t
         GROUP BY Col2) dt,
        (SELECT @rownum3 := 0) r) t1
    LEFT OUTER JOIN
    (SELECT dt.*, @rownum4 := @rownum4 + 1 AS rn
     FROM 
        (SELECT Col1
         FROM t
         GROUP BY Col1) dt,
        (SELECT @rownum4 := 0) r) t2
WHERE (t2.Col1 IS NULL);

You need distinct values of Col1 with a row-number.
You need also distinct values of Col2 with a row-number.
Now you can set a FULL OUTER JOIN by using UNION ALL over those distinct values ON row-numbers.

[SQL Fiddle Demo]

SQL select distinct but exhaustive columns

2 Answers2