How to do a join on the latest record? SQL

Question

Hello,

I'm making a query to grab the latest B record for each A record that is before the A record date. (I hope that makes sense)

And I have no idea how to do it! Please help!

My Final view will be

AID, AData, ADate, BData

Rough table schema

table A
(
AID,
ADate,
AData
)

table B
(
BID,
AID,
BDate,
BData
)

Things to note are:

1. Table A is in SQL DB
2. Table B is from link server to Oracle
3. Table B is super gigantic

Thanks!

Answer 1

This query would 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 buts. I don't have much experience with these 'linked tables' but I can imagine it going horribly wrong. You should try to figure out how MS SQL solves this kind of query. What you want to be sure of is that it can push down the subquery to the oracle side - you definitely do not want MS SQL to grab all the data over the wire and execute it on the MS SQL side.

If performance is an issue, you should probably try and do it the other way around - get a copy of the A table at the oracle side, solve the query there entirely an stuff that in a view, and then if you need that at the MSSQL side, link to that view.