Re: a wierd query

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: pgsql-sql@postgresql.org
> 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:ebacon@onesystem.com]
> > Sent: donderdag 13 mei 2004 17:28
> > To: sad
> > Cc: pgsql-sql@postgresql.org
> > 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 
> > 
> > ---------------------------(end of 
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> >                http://archives.postgresql.org
> > 
>