CODEWITHSUNDEEP
sql
user883288
user883288

Reputation: 11

Retrieving the latest transaction for an item

I have a table that lists lots of item transactions. I need to get the last record for each item (the one dated the latest).

For example my table looks like this:

Item    Date    TrxLocation
XXXXX   1/1/13   WAREHOUSE
XXXXX   1/2/13   WAREHOUSE
XXXXX   1/3/13   WAREHOUSE

aaaa    1/1/13   warehouse
aaaa    2/1/13   WAREHOUSE

I want the data to come back as follows:

XXXXX    1/3/13   WAREHOUSE
AAAA     2/1/13   WAREHOUSE

I tried doing something like this but it is bringing back the wrong date

select Distinct ITEMNMBR 
TRXLOCTN,
DATERECD
from TEST
where DateRecd = (select max(DATERECD)
        from TEST)

Any help is appreciated.

Upvotes: 1

Views: 94

Answers (2)

Art
Art

Reputation: 5792

No need for subquery. You are querying a single table and need to select MAX(date) and GROUP BY item and TrxLocation. 

SELECT Item, max(DATERECD) AS max_dt_recd, TrxLocation
 FROM test
GROUP BY Item, TrxLocation
/

Upvotes: 0

Simon Kingston
Simon Kingston

Reputation: 495

You're on the right track. You just need to change your subquery to a correlated subquery, which means that you give it some context to the outer query. If you just run your subquery (select max(DATERECD) from TEST) by itself, what do you get? You get a single date that is the latest date in the whole table, regardless of item. You need to tie the subquery to the outer query by linking on the ITEMNMBR column, like this:

SELECT ITEMNMBR, TRXLOCTN, DATERECD
FROM TEST t
WHERE DateRecd = (
  SELECT MAX (DATERECD) 
  FROM TEST tMax 
  WHERE tMax.ITEMNMBR = t.ITEMNMBR)

Upvotes: 2

Related Questions