Thread

1. DISTINCT ON () with UNION

   Per-Olof Pettersson <pgsql@peope.net> — 2001-05-13T23:14:04Z

   How can you use a distinct on () including the whole union.
   
   eg
   
   select distinct on (valutaid) valutaid, short from valuta UNION select 
   landid, land from land order by valutaid;
   
   table: valuta
   valutaid	valuta
   1		USD
   2		SEK
   
   table: land
   landid	land
   1		Sweden
   2		USA
   3		Norway
   
   The above would give
   1	USD
   1	Sweden
   2	SEK
   2	USA
   3	Norway
   
   What I would like to get is
   1	USD
   2	SEK
   3	Norway
   
   I did specify distinct on (valutaid) anyways =)
   
   Any suggestions?
   
   Best regards
   Per-Olof Pettersson
   
   

2. Re: DISTINCT ON () with UNION

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-14T00:52:39Z

   pgsql@peope.net writes:
   > How can you use a distinct on () including the whole union.
   
   In 7.1 you can write
   
   select distinct ... from (select ... union select ...) ss;
   
   			regards, tom lane