How can I modify/add to this join statement

Question

Viewing 2 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

bjohnson-533065 SSC Eights! Points: 929 More actions · Answer 1

Antares686 - it looks like that did the trick! This is great! Thank you all for your help!

I've said it before and I'll say it again - this site blows MSDN's forums out of the water 😀

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Although, the solution provided by Anteras should work, but it uses two instance of header table which can be avoided....

Here is what my code looks...

SELECTcs.customer_id, cs.customer_name, cs.DATE_ACCT_OPENED, l.invoice_no as last_invoice_no, l.INVOICE_DATE

FROMdbo.p21_view_customer cs

INNER JOIN

(

SELECTcustomer_id, invoice_no, INVOICE_DATE,

ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY INVOICE_DATE DESC ) AS RowNum

FROMdbo.p21_view_invoice_hdr

) l on cs.customer_id = l.customer_id and l.RowNum = 1

WHEREcs.delete_flag = 'N'

--Ramesh