retrieving max records from sqlserver

Question

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 :(

Answer 1

you could use a subquery

WHERE Detail.Date = (SELECT MAX(Date) FROM TransDet)

Answer 2

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)

Answer 3

SELECT  *
FROM    (
        SELECT  d.DocNumber, d.ProductId, t.TransDate,
        ROW_NUMBER() OVER (PARTITION BY d.DocNumber, d.ProductId ORDER BY TransDate DESC) AS rn
        FROM   tblTransDet d, tblTrans t
        WHERE  t.DocNumber = d.DocNumber
        )
WHERE rn = 1