CODEWITHSUNDEEP
sqloracle-databasejoin
user2010955
user2010955

Reputation: 4011

join two tables taking the one with max value

I'd like to join two table in a one-to-many relationship, but selecting only one row in the second table, the one with a max value at a column X of the second table.

Here is the sqlfiddle.

Here's my first solution:

select u.*, a.id, a.address 
from my_user u 
inner join my_address a on u.username = a.username
where a.id = (
  select max(id) from my_address a where a.username = u.username
);

Here's the second solution:

select * from (
  select 
    u.username, u.name, u.surname, a.id, a.address,
    row_number() over (partition by a.username order by a.id desc) rn
  from my_user u 
  inner join my_address a on u.username = a.username
) res 
where rn = 1;

Can you spot me the differences? Which is the best way and why? Are there any other better solutions?

Thanks!!

Upvotes: 0

Views: 3545

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Both your queries are okay for the task. None is really better than the other. But one may be faster than the other, which you can find out with EXPLAIN PLAN. Ideally Oracle would come up with the same execution plan for both, but it's a hard task for the optimizer to detect that both queries do the same thing.

As of Oracle 12c I'd use CROSS APPLY:

select u.*, a.id, a.address 
from my_user u
cross apply
(
  select *
  from my_address ma
  where ma.username = u.username
  order by ma.id desc
  fetch first row only
) a;

In earlier versions (as of Oracle 9i):

select u.*, a.id, a.address 
from my_user u
join
(
  select ma.*, row_number() over (partition by username order by id desc) as rn
  from my_address ma
) a on a.username = u.username and a.rn = 1;

In even earlier versions:

select u.*, a.id, a.address 
from my_user u
join
(
  select *
  from my_address ma
  where id in (select max(id) from my_address group by username)
) a on a.username = u.username;

Demo: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c0d3ab6617956cb69f979a413026f6db

Upvotes: 3

Related Questions