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
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? |