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