CODEWITHSUNDEEP
sqlsql-server
Admiral Land
Admiral Land

Reputation: 2492

Remove duplicate rows from one column

The problem is:

select (..)
UNION 
select (..)

Result is:

Col1, Col2, Col3

 Val1    Text1  Data
 Val1    Text2  Data

The problem is that i need to save only 1 row of this two. Col2 value is not same at fact, but the same in business logic.

So, how to get result like this:

Col1, Col2,Col3
Val1  Text1 Data

OR

Col1, Col2, Col3
Val1  Text2 Data

Thank you!

Upvotes: 2

Views: 353

Answers (2)

Charlieface
Charlieface

Reputation: 71579

You can place the UNION in a subquery and group again

SELECT
  Col1,
  MIN(Col2),
  Col3
FROM (
    SELECT Col1, Col2, Col3
    FROM table1 t1
    UNION ALL
    SELECT Col1, Col2, Col3
    FROM table2 t2
) t
GROUP BY
  Col1,
  Col2;

Note the use of UNION ALL rather than UNION, because you are grouping anyway it is not necessary to de-duplicate first.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269783

Hmmm . . . If you want one row per val, then one method is:

with t1 as ( < query 1 here > ),
     t2 as ( < query 2 here > )
select t1.*
from t1
union all
select t2.*
from t2
where not exists (select 1 from t1 where t1.val = t2.val);

Upvotes: 1

Related Questions