Getting the latest row from a batch

Hi All

This is a belter that my little brain can't handle.

Basically I have 1 SQL table that contains the following fields:

Stock Code
Stock Desc
Reference
Transaction Date
Qty
Cost Price

Basically this table stores all the transaction lines of when a user
books stock items into stock so that they can look at a journal of
this goods in as and when they please.

My task is that the user wants a list of all the stock items with the
last cost price that they were booked in at.

So I think I have to find the last transaction date used for each
stock code and then bring this back as 1 row per stock code with the
above fields of data.

How the whats-its can I do this? Is it acutally possible?

Any help you can give is much appreciated.

Rgds

LaphanSELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks David

Very much appreciated.

Rgds

Laphan

David Portas <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:nJmdnSNww5bmVkmi4p2dnA@.giganews.com...
SELECT S1.stockcode, S1.transdate, S1.costprice
FROM Stock AS S1
JOIN
(SELECT stockcode, MAX(transdate)
FROM Stock
GROUP BY stockcode)
AS S2(stockcode, transdate)
ON S1.stockcode = S2.stockcode
AND S1.transdate = S2.transdate
GROUP BY S1.stockcode, S1.transdate, S1.costprice

--
David Portas
----
Please reply only to the newsgroup
--|||Many thanks for the help David. The below script works perfectly
apart from the fact that I need to add a cost price field from another
table:

SELECT S1.STOCKID AS 'Stock Code', S1.TRANSACTIONDATE AS 'Transaction
Date', S1.QUANTITY AS 'Quantity', S1.COSTPRICE AS 'Cost Price'
FROM STOCKTRANSACTIONS AS S1
JOIN
(SELECT STOCKID, MAX(TRANSACTIONDATE)
FROM STOCKTRANSACTIONS
WHERE TRANSACTIONTYPE = 3
GROUP BY STOCKID)
AS S2(STOCKID, TRANSACTIONDATE)
ON S1.STOCKID = S2.STOCKID
AND S1.TRANSACTIONDATE = S2.TRANSACTIONDATE
GROUP BY S1.STOCKID, S1.TRANSACTIONDATE, S1.QUANTITY, S1.COSTPRICE

Could you please let me know how I can add an additional COSTPRICE
column from a STOCKPRICES table to this script.

FYR, if I was to perform a straightforward query to join the
STOCKTRANSACTIONS and the STOCKPRICES tables together this is how it
would look to get the required data, but this wouldn't contain the new
fangled 'find last date' thing that you sent me:

SELECT STOCKTRANSACTIONS.STOCKID, STOCKTRANSACTIONS.TRANSACTIONDATE,
STOCKTRANSACTIONS.QUANTITY, STOCKTRANSACTIONS.CURRENCYID,
STOCKTRANSACTIONS.COSTPRICE, STOCKPRICES.COSTPRICE
FROM STOCKPRICES, STOCKTRANSACTIONS
WHERE STOCKTRANSACTIONS.CURRENCYID = STOCKPRICES.CURRENCYID AND
STOCKTRANSACTIONS.STOCKID = STOCKPRICES.STOCKID AND
((STOCKTRANSACTIONS.TRANSACTIONTYPE=3) AND
(STOCKPRICES.PRICELEVELID='1'))

Any ideas on how to sync these 2 queries??

Rgds

Laphan