How do I group aggregated data a certain way

Question

I have the following sample transactional item receipt data, consisting of Item, Vendor and Receipt Date:

Item	Vendor	Receipt_Date
A	1	2021-01-01 00:00:00.000
A	2	2021-01-31 00:00:00.000
B	1	2021-02-01 00:00:00.000
B	2	2021-02-10 00:00:00.000
B	3	2021-02-20 00:00:00.000
C	7	2021-03-01 00:00:00.000

I want to select the Vendor for each Item, based on the last (max) Receipt Date, so the expected result for the above sample would be:

Item	Last_Vendor_For_Receipt
A	2
B	3
C	7

I can group the data per Item and Vendor, but I cannot figure out how to achieve the above expected result with an outer query. I'm using SQL Server 2012. Here's the initial query:

select
    ir.Item
    ,ir.Vendor
    ,max(ir.Receipt_Date) Last_Receipt_Date
from
    ItemReceipt ir

I checked online and in the forum, but it was hard to search for my specific question.

Thanks

Answer 1

Here is one approach using TOP with ROW_NUMBER:

SELECT TOP 1 WITH TIES *
FROM yourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Receipt_Date DESC);