Thread: UNION with more restrictive DISTINCT
I'd like to UNION two queries but the distinct-criteria for UNION should not be all columns in the queries, but only one. example. two tables: test=# select id,name from t1; id | name ----+------ 1 | bob 2 | mike (2 rows) test=# select id,name from t2; id | name ----+--------- 1 | bob 2 | mike j. (2 rows) # select id,name from t1 union select id,name from t2; id | name ----+--------- 1 | bob 2 | mike 2 | mike j. (3 rows) now I want a construct that returns me only one row for each id. If there are different names for that id's in the different tables, the name of t2 should be chosen. like: # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name) select id,name from t2; id | name ----+--------- 1 | bob 2 | mike j. (2 rows) What is an appropriate approach to this? If I use my UNION-query as subquery for a SELECT DISTINCT ID, I loose the name, which is important. thnx. peter -- mag. peter pilsl goldfisch.at IT-management tel +43 699 1 3574035 fax +43 699 4 3574035 pilsl@goldfisch.at
You probably want something more like: SELECT DISTINCT ON (id), * FROM ( <subquery1> UNION ALL <subquey2> ); The fact that UNION sorts at all is a side-effect of the implementation. The distinct part is part of the SQL spec. Use UNION ALL to get all the rows and then DISTINCT ON to do what you actually want... Hope this helps, On Wed, Dec 15, 2004 at 10:03:37PM +0100, peter pilsl wrote: > > I'd like to UNION two queries but the distinct-criteria for UNION should > not be all columns in the queries, but only one. > > example. two tables: > > test=# select id,name from t1; > id | name > ----+------ > 1 | bob > 2 | mike > (2 rows) > > test=# select id,name from t2; > id | name > ----+--------- > 1 | bob > 2 | mike j. > (2 rows) > > > # select id,name from t1 union select id,name from t2; > id | name > ----+--------- > 1 | bob > 2 | mike > 2 | mike j. > (3 rows) > > > now I want a construct that returns me only one row for each id. If > there are different names for that id's in the different tables, the > name of t2 should be chosen. > > like: > > # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name) > select id,name from t2; > id | name > ----+--------- > 1 | bob > 2 | mike j. > (2 rows) > > > What is an appropriate approach to this? If I use my UNION-query as > subquery for a SELECT DISTINCT ID, I loose the name, which is important. > > thnx. > peter > > > > -- > mag. peter pilsl > goldfisch.at > IT-management > tel +43 699 1 3574035 > fax +43 699 4 3574035 > pilsl@goldfisch.at > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
One solution is
SELECT COALESCE(t1.id,t2.id)
, COALESCE(t1.name,t2.name)
FROM t1 FULL JOIN t2 ON t1.id = t2.id
----- Original Message -----
From: "peter pilsl" <pilsl@goldfisch.at>
To: "PostgreSQL List" <pgsql-general@postgresql.org>
Sent: Wednesday, December 15, 2004 1:03 PM
Subject: [GENERAL] UNION with more restrictive DISTINCT
>
> I'd like to UNION two queries but the distinct-criteria for UNION should
> not be all columns in the queries, but only one.
>
> example. two tables:
>
> test=# select id,name from t1;
> id | name
> ----+------
> 1 | bob
> 2 | mike
> (2 rows)
>
> test=# select id,name from t2;
> id | name
> ----+---------
> 1 | bob
> 2 | mike j.
> (2 rows)
>
>
> # select id,name from t1 union select id,name from t2;
> id | name
> ----+---------
> 1 | bob
> 2 | mike
> 2 | mike j.
> (3 rows)
>
>
> now I want a construct that returns me only one row for each id. If
> there are different names for that id's in the different tables, the
> name of t2 should be chosen.
>
> like:
>
> # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name)
> select id,name from t2;
> id | name
> ----+---------
> 1 | bob
> 2 | mike j.
> (2 rows)
>
>
> What is an appropriate approach to this? If I use my UNION-query as
> subquery for a SELECT DISTINCT ID, I loose the name, which is important.
>
> thnx.
> peter
>
>
>
> --
> mag. peter pilsl
> goldfisch.at
> IT-management
> tel +43 699 1 3574035
> fax +43 699 4 3574035
> pilsl@goldfisch.at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Oops. That statement will "prefer" the t1.name instead of t2.name. It should
be COALESCE(t2.name,t1.name)
Another option is:
SELECT t2.id
, t2.name
FROM t2
UNION
SELECT t1.id
, t1.name
FROM t2
WHERE NOT EXISTS
(SELECT NULL
FROM t1 JOIN t2 ON t1.id = t2.id
)
----- Original Message -----
From: "Vincent Hikida" <vhikida@inreach.com>
To: "peter pilsl" <pilsl@goldfisch.at>; "PostgreSQL List"
<pgsql-general@postgresql.org>
Sent: Saturday, December 18, 2004 12:40 AM
Subject: Re: [GENERAL] UNION with more restrictive DISTINCT
> One solution is
>
> SELECT COALESCE(t1.id,t2.id)
> , COALESCE(t1.name,t2.name)
> FROM t1 FULL JOIN t2 ON t1.id = t2.id
>
>
> ----- Original Message -----
> From: "peter pilsl" <pilsl@goldfisch.at>
> To: "PostgreSQL List" <pgsql-general@postgresql.org>
> Sent: Wednesday, December 15, 2004 1:03 PM
> Subject: [GENERAL] UNION with more restrictive DISTINCT
>
>
>>
>> I'd like to UNION two queries but the distinct-criteria for UNION should
>> not be all columns in the queries, but only one.
>>
>> example. two tables:
>>
>> test=# select id,name from t1;
>> id | name
>> ----+------
>> 1 | bob
>> 2 | mike
>> (2 rows)
>>
>> test=# select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike j.
>> (2 rows)
>>
>>
>> # select id,name from t1 union select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike
>> 2 | mike j.
>> (3 rows)
>>
>>
>> now I want a construct that returns me only one row for each id. If there
>> are different names for that id's in the different tables, the name of t2
>> should be chosen.
>>
>> like:
>>
>> # select id,name from t1 UNION ON (id) AND SUPERMAGICPREFER (t2.name)
>> select id,name from t2;
>> id | name
>> ----+---------
>> 1 | bob
>> 2 | mike j.
>> (2 rows)
>>
>>
>> What is an appropriate approach to this? If I use my UNION-query as
>> subquery for a SELECT DISTINCT ID, I loose the name, which is important.
>>
>> thnx.
>> peter
>>
>>
>>
>> --
>> mag. peter pilsl
>> goldfisch.at
>> IT-management
>> tel +43 699 1 3574035
>> fax +43 699 4 3574035
>> pilsl@goldfisch.at
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that your
>> message can get through to the mailing list cleanly
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>