Getting value from MAX(Date) Row in SQL Server

Question

I am trying to get the last supplier of an item, by using the MAX function. What I am trying to achieve is showing what the supplier name was for the row with the MAX(Date) for all the stock items (shown below as account links).

The code I am using bring up multiple dates for the same accountlink, and I am struggling to see why. My code is:

SELECT 
  MAX(TxDate) AS Date,
  ST.AccountLink,
  V.Account AS Supplier 

FROM _bvSTTransactionsFull AS ST 
  JOIN Vendor V on ST.DrCrAccount = V.DCLink

WHERE Module = 'AP' 
AND Id = 'OGrv'
GROUP BY ST.AccountLink, V.Account
ORDER BY AccountLink

But my results look like the below

question from:https://stackoverflow.com/questions/65934500/getting-value-from-maxdate-row-in-sql-server

Answer 1

Try this out

select AccountLink,Supplier,date from(SELECT 
ST.AccountLink,
V.Account AS Supplier,
TxDate as [date],
row_number()over(partition by ST.AccountLink order by TxDate desc)rownum

FROM _bvSTTransactionsFull AS ST 
  JOIN Vendor V on ST.DrCrAccount = V.DCLink

WHERE Module = 'AP' 
AND Id = 'OGrv')t
where t.rownum = 1

The group by has been removed and ranking function is used to achieve the output