How to select field from a row using MAX on another column?

Question

I want to use an outer join from table A to a row on table B (which may not exist). I need to join on a date field in table B, which should be taken from the row which has the highest sequence number.

e.g.

Table B:

ID | A_DATE     | SEQNO
1    01/01/1950   10
1    01/01/2000   5

So here I would need to join A and B for ID 1 using A.id = 1 and A.a_date = 01/01/1950 (the highest sequence number for ID 1).

I don't think this syntax is correct:

SELECT ...
FROM a, b
WHERE a.id (+) = b.id
  AND ...

I'm lost - can anyone help?

Thanks!

Answer 1

You can get the a_date associated with the maximum value of seq_no by using the keep keyword:

select ...
from a join
     (select max(A_DATE) keep (dense_rank first order by seq_no desc) as max_a_date
      from b
     ) bmax
     on a.a_date = b.max_a_date;

The rest of the query just joins to this date. This query does not need an outer join, at least as I understand the problem.

Note: this assumes that table b exists. You cannot write a regular SQL query that works regardless of whether or not a table exists.

Answer 2

I don't know if I have correctly understood.

Try this:

select ...
from (select id,
              max(A_DATE) over(partition by id) as a_date
      from a) a,
     b
where a.id     = b.id (+)
  and a.a_date = b.b_date (+);