asked on

Query Syntax needed

I want to bring out the latest price for a number of items from a table. In MsAccess I can use the Last() function:

SELECT StockCode.StockName, Last(UnitCost) AS Cost
FROM PurchaseOrderDetails GROUP BY StockCode,StockName

The last() function is not available in SQL Server, but in my table there is an Identity RecordNo field. So I could use the Max() value of that, but I'm unsure of how to bring out the UnitCost field. If I include it within the Group By clause, I get every different price for each item. If I omit it from the Group By clause I get an error. I just want it to select the UnitCost which is on the record with the maximum value in RecordNo. So how do I modify the following statement in order to make it work?

SELECT Max(REcordNo), StockCode.StockName,UnitCost AS Cost
FROM PurchaseOrderDetails GROUP BY StockCode,StockName

Ephraim Wangoya

try
SELECT REcordNo, StockCode.StockName, UnitCost AS Cost
FROM PurchaseOrderDetails A
inner join (select max(recordno) from PurchaseOrderDetails GROUP BY StockCode, StockName) B on A.RecordNo = B.RecordNo

Ephraim Wangoya

Correction

SELECT  RecordNo,  StockCode, StockName, UnitCost AS Cost
FROM PurchaseOrderDetails A 
inner join (select max(recordno) [RecordNo] from PurchaseOrderDetails GROUP BY StockCode, StockName) B on A.RecordNo = B.RecordNo

Open in new window

TownTalk

ASKER

Almost there I think. It gives an error: Ambiguous column name 'RecordNo'.

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TownTalk

ASKER

Ok that works nicely now thank you.

Ian