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 ==
