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 🙁
Use a corelated subquery - this is untested: