On 17 Aug 2006 at 10:00, Mario Weilguni wrote:
> not really sure if this is right without any testdata, but isn't that what
> you
> want?
>
> CREATE index foo on sheep_flock (flock_no);
>
> SELECT DISTINCT on (f1.transfer_date) f1.regn_no, f1.transfer_date as date_in
> FROM SHEEP_FLOCK f1
> WHERE f1.flock_no = '1359'
> order by f1.transfer_date desc;
>
> best regards,
> mario weilguni
>
>
Mario, Thanks for the suggestion, but this query produces the wrong answer -
but
then I provided no data, nor properly explained what the data would be.
Each sheep will have multiple records, starting with one for when it's first
registered, then one for each flock it's in (eg sold into) then one for when it
dies
and goes to the 'big flock in the sky'.
So first I need to find the most recent record for each sheep and then select
the
sheep who's most recent record matches the flock in question.
Your query finds all the sheep that have been in the flock in question, then
selects
the first one from each set of records with the same date. So it collects data
on
dead sheep, and only selects one sheep if several were bought or registered on
the same day.
Forgive me for being verbose - I want to make sure I understand it propely
myself!
regards,
--
Peter Hardman
Acre Cottage, Horsebridge
King's Somborne
Stockbridge
SO20 6PT
== Breeder of Shetland Cattle and Shetland Sheep ==
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
