Selecting Last record from Joined Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting Last record from Joined Table

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2010-05-06 : 16:58:16

Hello,

I have a situation where I need to find the last line item from the last transaction for each invoice.

Here's the setup (NOTE: I did not design the table or database):

Invoice table is linked to Transaction table by CustID and TransID.

-------------
Invoice Table
-------------
CustID
InvID
InvoiceStatus

-----------
Trans Table
-----------
CustID
InvID
TransactionDate
LineItemID (smallint)

SAMPLE DATA

Invoice
1, 3, Open
1, 4, Closed
2, 3, Open
3, 1, Open

Trans
1, 3, 04-30-2010, 1
1, 3, 04-30-2010, 2
1, 3, 04-30-2010, 3
2, 3, 05-01-2010, 1
2, 3, 05-01-2010, 2
3, 1, 05-01-2010, 1

So the query should return:
1, 3, Open, 1, 3, 04-30-2010, 3
2, 3, Open, 2, 3, 05-01-2010, 2
3, 1, Open, 3, 1, 05-01-2010, 1

I need to be able to link the record with the largest LineItemID AND latest TransactionDate for all 'Open' invoices in the Invoice table.

The DBMS is MS-SQL 2005.

Thanks for any ideas.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-06 : 17:36:55

Try this

select a.CustID,a.InvID,a.InvoiceStatus,b.TransactionDate,b.LineItemID
from Invoice a inner join
(
select row_number () over(partition by CustID,InvID order by LineItemID desc,TransactionDate desc) as seq, * from Trans
) b
on a.CustID = b.CustID and a.InvID = b.InvID and b.seq = 1

MevaD
Starting Member

32 Posts

Posted - 2010-05-06 : 17:58:22

Yes!! It works. Thank you very much.

I appreciate you help.

-MD

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-07 : 10:09:11

No problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources