From: | "Stijn Vanroye" <s(dot)vanroye(at)farcourier(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Cc: | "Edmund Bacon" <ebacon(at)onesystem(dot)com> |
Subject: | Re: a wierd query |
Date: | 2004-05-17 07:31:02 |
Message-ID: | 71E201BE5E881C46811BA160694C5FCB0FA94F@fs1000.farcourier.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
> >
>
From | Date | Subject | |
---|---|---|---|
Next Message | Freddy Villalba Arias | 2004-05-17 08:02:18 | problem with spanish characters |
Previous Message | Stijn Vanroye | 2004-05-17 07:25:52 | Re: a wierd query |