Re: PostgreSQL runs a query much slower than BDE and MySQL

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