Sql – retrieving max records from sqlserver

sqlsql-server

I have been toiling with this for a while now. so decided to ask and get some help.

I have two tables as such:

tblTrans: (DocNumber field is always unique)

DocNumber       TransDate      userId
66-FF-GHIP      03-05-08       someUser
55-RT-JHTP      03-09-09       someOtherUser
77-AF-KPWT      05-08-09       userId1
09-IO-TEAG      04-08-09       thisUser


tblTransDet: (productIdCode field + DocNumber fields are always unique)
DocNumber       ProductIdCode      TransStatus   
66-FF-GHIP      4124               Approved
66-FF-GHIP      2124               Pending
66-FF-GHIP      2340               Approved
77-AF-KPWT      4124               Approved
55-RT-JHTP      4124               Pending
09-IO-TEAG      2124               Pending

As you can see. ProductIdCode 4124 is repeating multiple times in tblTransDet but combination of productIdCode and DocNumber is always unique.

How Can i retrieve docnumber and productID …based on the docNumber that has highest transDate.

So In this example. the final result would be

77-AF-KPWT    4124
09-IO-TEAG    2124
66-FF-GHIP    2340

I'd change the tablestrcuture if i could 🙁

Best Solution

Use a corelated subquery - this is untested:

SELECT
   d.DocNumber, d.ProductId, t.TransDate
FROM
   tblTransDet d, tblTrans t
WHERE
   d.DocNumber = t.DocNumber
AND
   t.TransDate = (SELECT MAX( TransDate) FROM tblTrans
                   WHERE DocNumber = d.DocNumber)
Related Question