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:[EMAIL PROTECTED]
> Sent: maandag 17 mei 2004 9:13
> To: [EMAIL PROTECTED]
> 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
> [EMAIL PROTECTED])
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
