Oracle select from table and join where date is oldest

Question

My real life problem is more comlpex, but the issue can be washed down to the following: I have one table with clients, and another table with clients' bank accounts. Now I want to do a select returning all clients that have bank accounts, joining with the bank account that was used least recently, ordered by the BANK_ID.

CLIENT:
CLIENT_ID    NAME
-----------------
1            JOE
2            BEN
3            SUE


BANK_ACCOUNT:
BANK_ID    CLIENT_ID    LAST_USED
-----------------------------------
1          2            Jan 1  2020
2          1            Mar 15 2020
3          2            Aug 5  2020
4          1            Feb 7  2020
5          1            Oct 13 2020

So Joe has three bank accounts, Ben has two, and Sue does not have any. The select should contain only Joe and Ben. Ben should go first because his "oldest" bank account is on BANK_ID 1 while Joe's is on BANK_ID 4.

BANK_ID   CLIENT_ID    NAME
---------------------------
1         2            BEN
4         1            JOE

I've been playing around with joins and subqueries, but I'm not sure what would be the best way to accomplish this query.

Thank you

Answer 1

A typical method is to use row_number():

select ba.bank_id, ba.client_id, c.name
from client c join
     (select ba.*,
             row_number() over (partition by client_id order by last_used) as seqnum
      from bank_account ba
     ) ba
     on ba.client_id = c.client_id and ba.seqnum = 1
order by ba.bank_id;

In Oracle, you can also use aggregation:

select max(ba.bank_id) keep (dense_rank over order by ba.last_used) as bank_id,
       ba.client_id, c.name
from bank_account ba
     on ba.client_id = c.client_id 
group by ba.client_id, c.name
order by ba.bank_id;