Re: UNION with more restrictive DISTINCT

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(at)goldfisch(dot)at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Martijn van Oosterhout <kleptog(at)svana(dot)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.