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!

I don't know your database schema, but joining on a date (non-unique row) sounds quite evil. Instead I would recommend you to google 'JOIN' and learn it yourself. It's not a topic one learns in a few minutes. — Fabian Bigler
– Fabian Bigler, Commented Feb 26, 2014 at 15:38

Gordon Linoff · Accepted Answer · 2014-02-26 15:49:49Z

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.

Corrado Piola · Accepted Answer · 2014-02-26 16:11:04Z

0

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 (+);

edited Feb 26, 2014 at 16:11

answered Feb 26, 2014 at 15:41

Corrado Piola

8791 gold badge14 silver badges18 bronze badges

2 Comments

ruakh Over a year ago

I'm pretty sure that should be last_value rather than max. I think max will select the greatest A_DATE (which won't be affected by the order by SEQNO at all) rather than the A_DATE with the greatest corresponding SEQNO. (Also, I'm pretty sure that the OP actually needs the subquery to be over B rather than over A, but the question is confusing.)

Corrado Piola Over a year ago

Yes, ORDER BY is not correct (I removed it). But my query gets the most recent Date, without considering the SEQNO. You are right, we must consider the date with the greatest SEQNO. If so, Gordon has given a good answer... :)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related