How to join the last entry? SQL

Question

I am making a request to capture the last record of B for each record of A, which is up to date of recording of A. (I hope this makes sense)

And I have no idea how to do this! Please, help!

My final view will be

AID, AData, ADate, BData 

Rough table layout

 table A ( AID, ADate, AData ) table B ( BID, AID, BDate, BData ) 

It should be noted:

• Table A is in SQL DB
• Table B from Oracle Link Server
• Table B - Super Giant

Thanks!

Answer 1

This query will do the trick:

 select a.*, curr.* from a inner join b curr on a.aid = curr.aid and a.adate > curr.bdate where curr.bdate = ( select max(b.bdate) from b where b.aid = curr.aid and b.bdate <= curr.bdate ) 

There are a few notes. I don’t have much experience with these “related tables”, but I can imagine that this is terribly wrong. You should try to figure out how MS SQL resolves this query. What you want to be sure of is that it can push the subquery to the side of the oracle - you definitely do not want MS SQL to capture all the data on the wire and execute it on the MS SQL side.

If performance is a problem, you probably should try and do it the other way around - get a copy of table A from the oracle side, allow the query there completely, which is in the view, and then if you need that on the MSSQL side, this view is referenced.