October 30th, 2013, 04:42 PM
-
Last transaction query
Hi all!
I have a table with three fields, for all intents and purposes:
Item
XAction DateTime
XAction Type
There can be multiple xactions per item.
I need a SQL that will give me the LAST transaction for each item.
Now, if I am looking for ONE item, that is simple. But I need this for ALL items... basically I want the item, with the last xaction and the date time of that last transaction.
Any ideas?
October 31st, 2013, 10:22 AM
-
Code:
SELECT t.Item
, t.XActionDateTime
, t.XActionType
FROM ( SELECT Item
, MAX(XActionDateTime) AS latest
FROM ThreeFieldTable
GROUP
BY Item ) AS m
INNER
JOIN ThreeFieldTable AS t
ON t.Item = m.Item
AND t.XActionDateTime = m.latest