Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Syntax needed

Posted on 2013-12-04
5
Medium Priority
?
170 Views
Last Modified: 2013-12-04
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
Comment
Question by:TownTalk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 39695294
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 39695302
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
 

Author Comment

by:TownTalk
ID: 39695335
Almost there I think. It gives an error: Ambiguous column name 'RecordNo'.
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 39695406
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
 

Author Comment

by:TownTalk
ID: 39695461
Ok that works nicely now thank you.

Ian
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question