Subject Re: [firebird-support] please help with group by and subselect
Author Helen Borrie
At 12:18 PM 18/09/2003 +0200, you wrote:
>Helen Borrie wrote:
> > Will this do?
> >
> > select
> > a.supplier,
> > a.last_quoted_price,
> > a.last_quote_date
> > from pricelist a
> > where a.last_quote_date = (
> > select max(b.last_quote_date) from pricelist b
> > where b.supplier = a.supplier
> > and b.product = a.product)
> > where a.product = :product
>If he has more than one record for same quote_date/suplier/product, he
>will get all of those. Perhaps to add "distinct" after select?

This will get the most recent if last_quote_date is a timestamp. If it's a
date-only and there's a possibility of multiple quotes per day, then
there's no way (among the given data) to find out which is the most recent.

Select distinct doesn't change anything, since it will return the first row
of the subset that meets the criteria...possibly the earliest (and thus not
the one you want) but you would have no way to know for certain.