Re: a wierd query

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(at)bankir(dot)ru]
> Sent: maandag 17 mei 2004 9:13
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
>