Raymond O'Donnell wrote:
On 09/10/2008 17:59, 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.
Hmmmm....
Leaving that aside, how about SELECTing the columns you want to be
unique from the two tables, and then JOINing the UNION of those back
with the UNION of the complete (three-column) tables?
No, this won't work. Here's an example of my tables:
Table1
1, 12, A
2, 16, B
8, 6, A
19, 9, C
Table2
1, 13, D
2, 16, B
8, 6, B
12, 5, A
A simple UNION will remove the duplicate row 2, 16, B, but it won't
block row 8, 6, B in table 2 from being included in the output. What I
want is for records in table 1 to take precedence and for the output
records to be unique based only on the first two columns. In other
words, I want this output:
Table3
1, 12, A
2, 16, B
8, 6, A
19, 9, C
1, 13, D
12, 5, A
Trying your suggestion:
create table tmp as
select col1, col2 from table1
union
select col1, col2 from table2;
create table tmp2 as
select * from table1
union
select * from table2;
I'll get:
tmp
1, 12
2, 16
8, 6
19, 9
1, 13
12, 5
tmp2
1, 12, A
2, 16, B
8, 6, A
19, 9, C
1, 13, D
8, 6, B
12, 5, A
I now have two rows with the same first two columns (8,6,A and 8,6,B and
if I try to JOIN tmp and tmp2 I'm going to get duplicates on the 8,6
key. Or am I misunderstanding what you suggested?
