Sorry, I forgot one situation:
if I run the query like so: select distinct on (task_id, begindate) task_id, workhour_id, begindate as date from workhour UNION select distinct on (task_id, enddate) task_id, workhour_id, enddate as date from workhour I get yet another value: 2961 rows. So I got 3 different result sets for 3 different ways to run the query. Even in this last case the UNION doesn't seem to only return unique values, and I will still need the top-level select. > -----Original Message----- > From: Stijn Vanroye > Sent: maandag 17 mei 2004 9:26 > To: [EMAIL PROTECTED] > Cc: 'Edmund Bacon' > Subject: RE: [SQL] a wierd query > > > Are you sure about that Edmund? > > I have the following query: > select distinct on (task_id, date) task_id, workhour_id, date from > ( > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > )as dist1 > which returns me 2763 rows in my case > > if I use the query without the top level select, like this: > select task_id, workhour_id, begindate as date from workhour > UNION > select task_id, workhour_id, enddate as date from workhour > I get 7146 rows. > > If I understand correctly there would be no need for the top > level select if UNION would be to only return unique values. > But given my test results this doesn't seem to be the case. > Am I missing something or am I misinterpreting something? I > mean I'm sure you get this information out of the > documentation, that's why this question has risen. > > > Kind regards, > > Stijn Vanroye > > > -----Original Message----- > > From: Edmund Bacon [mailto:[EMAIL PROTECTED] > > Sent: donderdag 13 mei 2004 17:28 > > To: sad > > Cc: [EMAIL PROTECTED] > > Subject: Re: [SQL] a wierd query > > > > > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > > > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > > > > > > -- > > Edmund Bacon <[EMAIL PROTECTED]> > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])