title: Join only the lowest row with the nearest row from another table SQL
body: <p>I have two table in Teradata like below:</p>
  <p>Table A</p>
  <pre><code>A|B|ID|C |D
  1|2|11|hh|
  1|2|31|zz|ss
  1|2|21|ee|nn
  </code></pre>
  <p>Table B</p>
  <pre><code>A|B|ID|C |D |dat
  1|2|10|yy|tt|'2021-01-01'
  1|2|07|tt|uu|'2021-01-01'
  1|3|12|ee|oo|'2021-01-02'
  </code></pre>
  <p>output</p>
  <pre><code>A|B|ID|C |D
  1|2|11|hh|yy
  1|2|21|ee|hh
  1|2|31|zz|ee
  </code></pre>
  <p>Explanation</p>
  <p>The goal is to calculate column D value so, we start with rows from table A (which is a with query in fact)
  for the same A and B we look for the anterior row in ID values and we get the value of C which will be the D value of current row,
  Till we come to the lowest row in ID value (11 in the example) in this case we look in the table B for the max(dat) for the same A and B ( if there is multiple rows with the same max(dat) we get the row contain the max(ID) with id &lt; id of the current row (11 in the example)</p>
  <p>I tried (the query below) to get the result with the Lag function but I can't do the join for only the lowest id from table A with the row of table B.</p>
  <pre><code>SELECT 
  a.A,
  a.B,
  a.ID,
  a.C,
  case when lag(a.A,1)over(partition by a.A,a.B order by a.id) isnot null then
  lag(a.D,1)over(partition by a.A,a.B order by a.id) else b.c end as D 
  
  from 
  table_a a
  left join ( select A,B,C,id,dat
  from 
  table_b) b
  on a.A=b.A
  and a.B = b.B
  and a.id &gt; b.id
  
  qualify row_number () over(partion by b.A,b.B order by b.dat desc,b.id desc) = 1
  </code></pre>