From: Stijn Vanroye <s.vanroye@farcourier.com>
To: pgsql-sql@postgresql.org
Cc: sad <sad@bankir.ru>
Subject: Re: a wierd query
Date: Mon, 17 May 2004 11:24:33 +0200
Message-ID: <71E201BE5E881C46811BA160694C5FCB046739@fs1000.farcourier.com> (raw)
First of all, I don't select distinct on 1 value, but on 2. Meaning I want each unique combination of task_id (or employee_id in this example) and date. That way both fields still have meaning.
the workhour_id field is indeed redundant, but was still there from some pevious testing work. (It is now removed). both other fields are used.
What I'm trying to achieve here is the following: for each task get all date's in wich that task has been performed (and each date only once per task). Since workhours have a begin date and time, as well as an end date and time. Begin- and enddate don't have to be the same day. But if a task is performed starting monday and lasting till tueseday, both days have to be included in the count.
What I use now is this:
select distinct on (date, employee_id) employee_id, date from
(
select distinct on (begindate, employee_id) begindate as date, employee_id from workhour
UNION
select distinct on (enddate, employee_id) enddate as date, employee_id from workhour
)as dist
The workhour table looks something like this:
workhour(workhour_id, task_id, employee_id, begindate, begintime, enddate, endtime)
I Can't think of any other solution to achieve this. As far as I can tell, Im not missing something and I don't have meaningless fields (suggestions always welcome). Later on some grouping will be done to count the number of days worked on a certain task (or by a certain employee) in a given period.
This still keeps my question open wether or not a UNION does only show unique values in the union.
> -----Original Message-----
> From: sad [mailto:sad@bankir.ru]
> Sent: maandag 17 mei 2004 9:13
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] a wierd query
>
>
> > 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.
>
> if we suppose this situation possible to program in SQL
> it causes data-loss in query
> (i mean unpredictable query result:
> if you have two records (f=1,b=2),(f=1,b=3)
> "distinct ON f" makes a value of b meaningless)
>
> So you MUST NOT select that way
>
> P.S. This situation means: you have wrong data structure.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: s.vanroye@farcourier.com, sad@bankir.ru
Subject: Re: a wierd query
In-Reply-To: <71E201BE5E881C46811BA160694C5FCB046739@fs1000.farcourier.com>
References: <71E201BE5E881C46811BA160694C5FCB046739@fs1000.farcourier.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox