How do I group aggregated data a certain way-CodePudding

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

CodePudding user response：

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);

CodePudding user response：

First you select the desired max date per item:

select max(Receipt_Date) as max_rcpt_date
,      Item
from   your_unknown_table
group by Item

And then you can use this as a subquery to get the vendor:

select Item
,      Vendor
from   your_unknown_table   
where  ( Receipt_Date, Item ) in 
             ( select max(Receipt_Date) as max_rcpt_date
               ,      Item
               from   your_unknown_table
               group by Item
             )

This will work in Oracle. I'm not sure if this subquery-structure in SQL-Server wil work.