Re: [GENERAL] UNION with more restrictive DISTINCT
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 [EMAIL PROTECTED] To: peter pilsl [EMAIL PROTECTED]; PostgreSQL List [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To: PostgreSQL List [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 [EMAIL PROTECTED] To: PostgreSQL List [EMAIL PROTECTED] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Martijn van Oosterhout [EMAIL PROTECTED] 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. pgpdCjyIx6Lqb.pgp Description: PGP signature
