Re: Question About UNION

On Thu, 2008-10-09 at 10:59 -0600, Bill Thoen wrote:
> >> I'm trying to combine two tables, but I only want unique records based
> >> on the first two columns. Can UNION be used to join three-column tables
> >> but only include records based on the uniqueness of the first two
> >> columns? If not, how would I do this with PostgreSQL 8.1?
> >
> > How do you decide which records you want? - e.g. given the following rows...
> >
> >   (a, b, c)
> >   (a, b, d)
> >
> > ...how do you decide whether you the one with c or the one with d?
> >
> >
> The physical order that they appear will take care of that.

If the rest of the columns don't matter, how about:

SELECT DISTINCT ON(col1, col2) * FROM (
 SELECT col1, col2, col3 FROM table1
  UNION
 SELECT col1, col2, col3 FROM table2
 ORDER BY col1, col2
) AS uniontable;

- Josh Williams