X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E2326D1B330 for ; Mon, 17 May 2004 04:31:36 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 77402-06 for ; Mon, 17 May 2004 04:31:17 -0300 (ADT) Received: from mail.cobweb.nl (mx1.cobweb.nl [80.242.96.4]) by svr1.postgresql.org (Postfix) with ESMTP id 5356CD1B2C2 for ; Mon, 17 May 2004 04:31:15 -0300 (ADT) Received: from farcourier.com (dp3-26.dsl.cobweb.nl [193.67.120.154] (may be forged)) by mail.cobweb.nl (8.12.8/8.12.7) with ESMTP id i4H7VLu1076920 for ; Mon, 17 May 2004 09:31:21 +0200 (CEST) (envelope-from s.vanroye@farcourier.com) Subject: Re: a wierd query Date: Mon, 17 May 2004 09:31:02 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-ID: <71E201BE5E881C46811BA160694C5FCB0FA94F@fs1000.farcourier.com> X-MS-Has-Attach: X-MIMEOLE: Produced By Microsoft Exchange V6.0.6249.0 Content-Class: urn:content-classes:message X-MS-TNEF-Correlator: Thread-Topic: [SQL] a wierd query Thread-Index: AcQ5AI4owP6tl52WQ0OfrpXPEpdSAAC3tbdwAABNnsA= From: "Stijn Vanroye" To: Cc: "Edmund Bacon" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/141 X-Sequence-Number: 17855 Sorry,=20 I forgot one situation: if I run the query like so: select distinct on (task_id, begindate) task_id, workhour_id, beginda= te as date from workhour UNION select distinct on (task_id, enddate) task_id, workhour_id, enddate a= s 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. Eve= n in this last case the UNION doesn't seem to only return unique values, an= d I will still need the top-level select. > -----Original Message----- > From: Stijn Vanroye=20 > Sent: maandag 17 mei 2004 9:26 > To: pgsql-sql@postgresql.org > Cc: 'Edmund Bacon' > Subject: RE: [SQL] a wierd query >=20 >=20 > Are you sure about that Edmund? >=20 > 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 >=20 > 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. >=20 > If I understand correctly there would be no need for the top=20 > level select if UNION would be to only return unique values.=20 > But given my test results this doesn't seem to be the case.=20 > Am I missing something or am I misinterpreting something? I=20 > mean I'm sure you get this information out of the=20 > documentation, that's why this question has risen. >=20 >=20 > Kind regards, >=20 > Stijn Vanroye >=20 > > -----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 > >=20 > >=20 > > sad wrote: > > > select distinct a as F from table > > > union > > > select distinct b as F from table; > > > > >=20 > > Note that UNION only returns the unique values of the union > > You can get repeated values by using UNION ALL. > >=20 > >=20 > > --=20 > > Edmund Bacon > >=20 > > ---------------------------(end of=20 > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > >=20 > > http://archives.postgresql.org > >=20 >=20