Sql Join With Selecting Previous Matching Value With Group By

I have 2 tables that I want to join on a key that matches with the first previous record. I have a query that does this. However I also want to include a column in the results from

Solution 1:

SELECT A.*, t1.stringAS string1, t2.stringAS string2
FROM
(SELECT t1.frame AS frame1, MAX(t2.frame) AS frame2
FROM t1
INNER JOIN t2 ON t1.key=t2.keyAND t2.frame< t1.frame
GROUPBY t1.frame
) A
INNER JOIN t1 ON A.frame1=t1.frame
INNER JOIN t2 ON A.frame2=t2.frame;

Output:

    frame1  frame2  string1 string2
1   51      6       text13  text17
2   107253  106999  text25  text39

Solution 2:

THis query strips the tables down to "just the latest row" where "latest" is defined as "having the highest int value for the key column".

That's what the row_number() over() function does; assigns an incrementing number to a row, restarts it whenever key changes, and rows with the same key are ordered by frame descending, so the latest is always rownumber 1

SELECT 
    a.frame as frame1,
    a.stringas string1,
    b.frame as frame2  
FROM 
  (SELECT 
   frame, 
   key, 
   string, 
   row_number() over(partition bykeyorderby frame desc) as rown
   from t1
  ) a 
  INNER JOIN 
  (SELECT 
   frame, 
   key, 
   string, 
   row_number() over(partition bykeyorderby frame desc) as rown
   from t2
  ) b
  ON a.rown = 1and a.key = b.keyand b.rown=1

if you need to change the definition of "latest" then change the order by to be ascending (it will give the lowest number of frame)

If as per my comment your definition of "first previous" differs, i.e. you want the row before the latest, (where a higher key number is "later") then make it rown = 2 in the ON clause, and make the order by to be key descending

(Perhaps it will help you if you just run the subqueries on their own, then look at the data and say "the rows I want always have a rown of X")

Update:

I suspect from your recent update that you want the ON clause to be where rown=2 for probably one of your tables if not the other. Because it's not clear to me which one of your tables is "behind" you'll have to edit the answer above a bit in sqlfiddle.. Here's a version that produces your requested output

SELECT 
    a.frame as frame1,
    a.stringas string1,
    b.frame as frame2,
    b.stringFROM 
  (SELECT 
   frame, 
   key, 
   string, 
   row_number() over(partition bykeyorderby frame desc) as rown
   from t1
  ) a 
  INNER JOIN 
  (SELECT 
   frame, 
   key, 
   string, 
   row_number() over(partition bykeyorderby frame desc) as rown
   from t2
  ) b
  ON a.rown = 2and a.key = b.keyand b.rown=1

You might want to test this on larger data sets

Solution 3:

http://sqlfiddle.com/#!17/47c11/2

selectdistincton (t.frame1, t.key1, t.string1)
    t.* 
from
    (select 
        t1.frame frame1, t1.key key1, t1.string string1, t2.frame frame2, t2.key key2, t2.string string2
    from 
         t1 
    join 
         t2 
    on 
         t1.key=t2.keyand t1.frame > t2.frame
    orderby 
         t2.frame desc) t