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]