[RESOLVED] select distinct rows after UNION

[lli2k5]

I have table A and table B.

How do I SELECT distinct rows from the union of table A and B. Assume that I want to select distinct on row 'link'. However, each row has more than just the 'link' field.

[tomhath]

Put the UNION inside a subselect:

select distinct u.link 
from (
  select link from table1
  UNION
  select link from table2
    ) as u

[Lars_Berg]

select link, max(c1),max(c2)
  from (
select link,c1,c2 from t1
  UNION
  select link,c1,c2 from t2 ) dt
  group by link

@:
That will not make any difference since union will remove any duplicate.

[tomhath]

Lars -

Yup, forgot all about that.

So lli2k5, isn't the UNION enough?

-Tom

[lli2k5]

For some reason,

I did the union and the same rows appeared in both sets.

[lli2k5]

The thing is I want to do a UNION on the link field. However, I want to retain all the other fields for that row, so what i do is:

select link,a,b,c FROM table1 UNION select link,a,b,c FROM table2

After doing that, duplicates appear in the union.