Hi again, Bhavbhuti!

I normally do this similar to:

SELECT v1.iAcctID, v1.cAccount, v1.iItemID, v1.cItem, v1.dDt, v1.bRate
FROM vwPriceList v1
WHERE NOT EXISTS(SELECT * FROM vwPriceList v2
WHERE v1.cAccount = v2.cAccount
AND v1.cItem = v2.cItem
AND v1.dDt < v2.dDt)
ORDER BY 2, 4, 5 DESCENDING

If you use Firebird 2.1, then you can alternatively use

WITH Tmp as
(SELECT cAccount, cItem, max(dDt) as dDt
FROM vwPriceList
GROUP BY 1, 2)

SELECT v.iAcctID, v.cAccount, v.iItemID, v.cItem, v.dDt, v.bRate
FROM vwPriceList v
JOIN Tmp t
ON v.cAccount = t.cAccount
AND v.cItem = t.cItem
AND v.dDt = t.dDt
ORDER BY 2, 4

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of venussoftop
Sent: 3. desember 2009 14:56
To: firebird-support@yahoogroups.com
Subject: [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