For each Stock Bid select the related Bidder's Most Recent SignIn

Question

I know similar has been asked and answered previously but despite hours on it I still can't figure out how to

Select StockBids.*  along with the 
MOST RECENT MemberSignIns.Lat/Long 
from the following two tables
 wWhere StockBids.MemberID = 'MSFT' 

see http://sqlfiddle.com/#!6/7b9c7/2 for test data

As full explanation returned should be rows for StockBids.ID = 24, 26 28 AND because MemberID 4 submitted both StockBid.ID = 24 and 28 these two rows should have Lat/Long values of 40.692259/-73.766641 as derives from his latest MemberSignin.ID = 16.

StockBids
ID  MemberID    EntityAbbrev    BidPrice    BidShares
24    4         MSFT          7.43        10
25    4         AAPL          7.43        10
26    5         MSFT          7.36        20
27    6         AAPL          7.29        30
28    4         MSFT          7.22        40

JOINED ON StockBids.MemberID = MemberSignIns.MemberID

MemberSignIns
ID  MemberID    UTC             Lat       Long
11    4      6/23/17 16:23     40.736999    -73.875247
12    4      6/23/17 16:32     40.782117    -73.980739
13    5      6/23/17 16:40     40.643339    -73.977714
14    6      6/23/17 16:48     40.760262    -73.993287
15    7      6/23/17 16:56     40.722414    -73.992452
16    4      6/23/17 17:04     40.692259    -73.766641

Answer 1

Try to use the following

SELECT s.*,m.*
FROM
  (
    SELECT *,ROW_NUMBER()OVER(PARTITION BY MemberID ORDER BY ID) RowNum
    FROM StockBids
  ) s
JOIN
  (
    SELECT *,ROW_NUMBER()OVER(PARTITION BY MemberID ORDER BY ID) RowNum
    FROM MemberSignIns
  ) m
ON s.MemberID=m.MemberID AND s.RowNum=m.RowNum

First of all I added an additional number into each rows using ROW_NUMBER. See two subqueries

SELECT *,ROW_NUMBER()OVER(PARTITION BY MemberID ORDER BY ID) RowNum
FROM StockBids

SELECT *,ROW_NUMBER()OVER(PARTITION BY MemberID ORDER BY ID) RowNum
FROM MemberSignIns

I used ID for rows order and added partitions by members.

And after that I do JOIN using MemberID and these additional numbers.

But maybe I misunderstood you at first. If you want to use last Lat and Long from MemberSignIns you can use OUTER APPLY

SELECT s.*,l.Lat,l.Long,l.TestID
FROM StockBids s
OUTER APPLY
  (
    SELECT TOP 1 m.Lat,m.Long,m.ID TestID
    FROM MemberSignIns m
    WHERE m.MemberID=s.MemberID
    ORDER BY m.ID DESC -- or ORDER BY m.UTC DESC - if ID isn't consistent with UTC.
  ) l