UNION with more restrictive DISTINCT

From: peter pilsl <pilsl(at)goldfisch(dot)at>
To: PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: UNION with more restrictive DISTINCT
Date: 2004-12-15 21:03:37
Message-ID: 41C0A6A9.4080703@goldfisch.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)goldfisch(dot)at

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-12-15 21:08:43 Re: could not create semaphores : No space left on device = FreeBSD port install error!
Previous Message Dave Brazzeal 2004-12-15 20:42:15 is there a repair utility for postgresql?