• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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
0
TownTalk
Asked:
TownTalk
  • 3
  • 2
1 Solution
 
Ephraim WangoyaCommented:
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
0
 
Ephraim WangoyaCommented:
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

0
 
TownTalkAuthor Commented:
Almost there I think. It gives an error: Ambiguous column name 'RecordNo'.
0
 
Ephraim WangoyaCommented:
no problem, prefix it with A

SELECT  A.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

0
 
TownTalkAuthor Commented:
Ok that works nicely now thank you.

Ian
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now