Get row with latest date in group by

Question

I have table with invoice actions. One invoice can have multiple actions. I need to create a view with one row for each invoice. And that row should be the latest action. I have currently following:

SELECT  tbl.*
FROM [dbo].[Tester] tbl JOIN
      (SELECT TransNo, MAX(TransDate) AS MxDt
       FROM [dbo].[Tester]
       GROUP BY TransNo
      ) sub
      ON tbl.TransNo = sub.TransNo AND TransDate = MxDt;

I have seen multiple posts in here stating this is the way to do it. However I get multiple rows in the result set with same TransNo? Have I missed something?

Data set:

Result running query:

I would expect to only have one row for each transaction. Even when it has two with same dates then it should just take one.

Answer 1

I wouldn't recommend this approach but you need another condition in the join:

SELECT  tbl.TransNo
FROM [clm].[InvoiceView] tbl JOIN
      (SELECT TransNo, MAX(ActDate) AS MxDt
       FROM [clm].[InvoiceView]
       GROUP BY TransNo
      ) sub
      ON tbl.TransNo = sub.TransNo AND ActDate = MxDt;

I would instead recommend window functions:

select iv.*
from (select iv.*,
             row_number() over (partition by transno order by actdate desc) as seqnum
      from InvoiceView iv
     ) iv
where seqnum = 1;

or:

select iv.*
from InvoiceView iv
where iv.actdate = (select max(iv2.date)
                    from InvoiceView iv2
                    where iv2.transno = iv.transno
                   );