Skip site navigation (1) Skip section navigation (2)

Re: a wierd query

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 (view raw or flat)
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
> > 
> 

Responses

pgsql-sql by date

Next:From: Freddy Villalba AriasDate: 2004-05-17 08:02:18
Subject: problem with spanish characters
Previous:From: Stijn VanroyeDate: 2004-05-17 07:25:52
Subject: Re: a wierd query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group