Thanks a lot Elkins for the query.

--- In firebird-support@yahoogroups.com, "Elkins Villalona" <e_villalona@...> wrote:
>
> Maybe this is not fanciest way to do it but it works
>
>
>
> SELECT A.iAcctID, A.cAccount, A.iItemID, A.cItem, A.dDt, A.bRate
>
> FROM vwPriceList A
>
> WHERE (A.iAcctID||A.iItemID||A.dDt) = (SELECT
> MAX(B.iAcctID||B.iItemID||B.dDt)
>
> FROM vwPriceList B
>
> WHERE B.iAcctID = A.AcctID
>
> AND B.iItemID = A.iItemID)
>
>
>
>
>
> Elkins
>
>
>
> De: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] En nombre de venussoftop
> Enviado el: jueves, 03 de diciembre de 2009 09:56 a.m.
> Para: firebird-support@yahoogroups.com
> Asunto: [firebird-support] Return the First 1 for each Supplier + Item +
> Date combination
>
>
>
>
>
> Hi all
>
> I have a FireBird view which returns a set of data as mentioned below. Now I
> just want to pick the latest rates of each item from each supplier. My query
> (also below) I can't use FIRST 1 else I get only one record which is not
> correct.
>
> Please advise
> Bhavbhuti
>
> The whole view currently returns this set of data
> 1, ABC, 11, Bearing, 05.12.2009, 60
> 1, ABC, 11, Bearing, 01.12.2009, 55
> 1, ABC, 15, Bolt, 05.12.2009, 1.45
> 1, ABC, 15, Bolt, 01.12.2009, 1.50
> 1, ABC, 15, Bolt, 30.11.2009, 1.55
> 20, XYZ, 11, Bearing, 03.12.2009, 50
> 20, XYZ, 15, Bolt, 03.12.2009, 2
>
> My query of the view is below
> SELECT iAcctID, cAccount, iItemID, cItem, dDt, bRate
> FROM vwPriceList
> ORDER BY 2, 4, 5 DESCENDING
>
> I want the following result set only
> 1, ABC, 11, Bearing, 05.12.2009, 60
> 1, ABC, 15, Bolt, 05.12.2009, 1.45
> 20, XYZ, 11, Bearing, 03.12.2009, 50
> 20, XYZ, 15, Bolt, 03.12.2009, 2
>
>
>
>
>
> [Non-text portions of this message have been removed]
>