Re: a wierd query - Mailing list pgsql-sql

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_idin 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).
bothother 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
(andeach date only once per task). Since workhours have a begin date and time, as well as an end date and time. Begin-
andenddate don't have to be the same day. But if a task is performed starting monday and lasting till tueseday, both
dayshave 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
meaninglessfields (suggestions always welcome). Later on some grouping will be done to count the number of days worked
ona 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)
>