From: | Bill Thoen <bthoen(at)gisnet(dot)com> |
---|---|
To: | rod(at)iol(dot)ie |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question About UNION |
Date: | 2008-10-09 17:48:26 |
Message-ID: | 48EE43EA.6030000@gisnet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | David Wilson | 2008-10-09 17:54:44 | Re: Question About UNION |
Previous Message | Bill Thoen | 2008-10-09 17:19:56 | Re: Question About UNION |