CODEWITHSUNDEEP
t-sqljoinsql-server-2008-r2
NCollinsTE
NCollinsTE

Reputation: 309

SQL SELECT in another table with most recent date

I have a list of Matter data in Table1 that I need to query, as well as get the most recent Invoice Number in Table2 that is tied to the original Matter. I'm having extreme difficulty in joining these tables together and only getting one result for each Matter as I only want the most recent Invoice #.

Any and all help would be greatly appreciated.

Table1

enter image description here

Table2

enter image description here

RESULT

enter image description here

Upvotes: 0

Views: 439

Answers (1)

APH
APH

Reputation: 4154

The following assigns numbers to each invoice row in order of date, and selects only the most recent. Note that this assumes InvoiceDate is stored as a date,datetime, or something else that will sort chronologically, and that in the event of two invoices for the same date, returning either will be fine. If you need to return both invoices in the event of ties, replace row_number with rank.

Select * from Table1 a
inner join 
    (Select *
    , row_number() over (partition by MatterID order by InvoiceDate desc) as RN
     from Table2) b
on a.MatterID = b.MatterID and b.RN = 1

Upvotes: 0

Related Questions