>Hi All:
>
>Consider the following table of invoices, where I want to retrieve the price on the latest date.
>
>

>Barn      Date     Price
>BAA1     7/1/12  .24
>BAA2     7/2/12  .23
>BAA1     7/3/12  .22
>

>
>The following SQL will not work because it will return the price of .24:
>
>

>SELECT substr(fi_flcode,1,4) barn, MAX(fi_price), ;
>	MAX(fi_dlvdat) DeliveryDate ;
>	FROM feedinv ;
>	GROUP BY barn
>

>
>If I don't MAX the price, Iget a GROUP BY error. If I add price to the group, I get multiple records, one for each price.
>
>Solution?
>
>Thanks,
>
>Yossi

SELECT substr(feedinv.fi_flcode,1,4) AS barn,
       feedinv.fi_price, ;
       feedinv.fi_dlvdat AS DeliveryDate ;
FROM feedinv ;
INNER JOIN (SELECT substr(feedinv.fi_flcode,1,4) AS barn,;
                  MAX(feedinv.fi_dlvdat) AS dlvdat;
            FROM feedinv ;
            GROUP BY barn) Tbl1;
   ON substr(feedinv.fi_flcode,1,4) == Tbl1.Barn AND;
      feedinv.fi_dlvdat == Tbl1.dlvdat

>