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
toonly 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 <ebacon@onesystem.com>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>