2

Apparently outer-joins to a subquery are not allowed by Oracle. For each row on table A, I'm trying find the row on table B with the same ID, and latest date.

Something like this:

SELECT a.*, b.date, b.val1, b.val2
FROM a, b
WHERE b.id (+) = a.id
  AND b.date (+) = (SELECT MAX(b.date) FROM a, b WHERE a.id = b.id);

Removing the outer join (+) on b.date allows it to be parsed, but no rows are returned when there are no rows on table B. I need the query to just return NULL in this case. Is there a way around this?

Thanks

philipxy
  • 14,867
  • 6
  • 39
  • 83
James
  • 97
  • 2
  • 9
  • why can't you use `order by b.date desc` – arunb2w Feb 26 '14 at 16:40
  • Thanks - I need to select a couple other fields from B. Updated the question. – James Feb 26 '14 at 16:41
  • That's okay Since your concern is about the rows with latest date still u can use order by – arunb2w Feb 26 '14 at 16:43
  • Okay. I also need to select some matching rows from another table C. Could you give an example of how this would work? Thanks – James Feb 26 '14 at 16:45
  • You weren't totally clear about what you want to be null. Do you want the date from B to be null if it isn't the max? Or do you want everything from B to be null if the date isn't the max? – Douglas Barbin Feb 26 '14 at 17:01

4 Answers4

3

I think what you want is this:

SELECT a.*, b.date, b.val1, b.val2
FROM a
LEFT JOIN b ON b.id = a.id
WHERE (b.date is null 
       or b.date = (SELECT MAX(b2.date) FROM b b2 WHERE a.id = b2.id));

This way, the outer join is just performed on id. Then we're filtering out all of the rows where b.date is not the max for the corresponding row in a.

As an aside, you'll note that I removed a from the sub-query. As originally written, the sub-query returned the largest date in b that had a corresponding row in a. The same value would be used for every row of the outer query. The revised version makes the sub-query correlate to the outer query (i.e. it will get the corresponding max(date) for each row returned).

Allan
  • 17,141
  • 4
  • 52
  • 69
  • This is a good answer and I'm upvoting it. It might be more or less efficient than my answer depending on how many rows are in each table. – Douglas Barbin Feb 26 '14 at 16:58
  • @DouglasBarbin: Our answers actually provide different results. Yours gets a single `max(date)` across all of the `b` table and mine gets the `max(date)` for each unique `b.id`. It's unclear based on the question which is the correct interpretation. – Allan Feb 26 '14 at 17:01
  • I just commented something similar on the OP. – Douglas Barbin Feb 26 '14 at 17:02
  • Mine is also different in that it won't perform the join with B for the rows from A where the date isn't the max for the table. Yours will always join on id, then eliminate rows based on whether or not the date in B is the max for a given id. So the 2 queries are actually a bit more different than I initially realized. – Douglas Barbin Feb 26 '14 at 17:04
3

I already voted for Allan's answer, but just to demonstrate an alternative approach, here's how it can be done with an analytic function:

SELECT * FROM (
  SELECT a.*, b.date, b.val1, b.val2,
    ROW_NUMBER() over (PARTITION BY a.id ORDER BY b.date DESC) r
  FROM a LEFT JOIN b ON a.id=b.id
)
WHERE r=1

This will include only one row for each a.id, even if there are multiple b rows with the maximum date. To include all of them, change ROW_NUMBER to RANK.

0

Edit: You can save the max date to a variable

DECLARE @maxDate as datetime
SET @maxDate = (SELECT MAX(date) FROM b)

SELECT a.*, b.date, b.val1, b.val2
FROM a
LEFT OUTER JOIN b ON a.id = b.id
  AND b.date = @maxDate

This may be more or less efficient than Allan's answer, depending on if A has many more rows than B (or vice-versa). If B has a ton of rows, then querying it twice (which my answer does) is probably not the best solution.

Douglas Barbin
  • 3,595
  • 2
  • 14
  • 34
0

How about a scalar subquery?

select a.*, (select max(b.date) from b where b.id = a.id) as b_date
from a;
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • He needs a few other columns from B, and (I think) that he wants to only perform the join with the rows in B that have the max date. – Douglas Barbin Feb 26 '14 at 16:48
  • I think you're right - but when I answered his question didn't want any other columns from B, that was added later! – Tony Andrews Feb 26 '14 at 16:54