您可以使用CTE和排名功能PARTITION BY
:
WITH CTE AS
(
select t.ProdNo, t.TransactionDate as 'LastPurchaseDate', t.Price,
rn = row_number() over (partition by ProdNo order by TransactionDate desc)
from Transactions t
)
SELECT ProdNo, LastPurchaseDate, Price FROM CTE WHERE RN = 1