CODEWITHSUNDEEP
sqloracle-databaseoracle11ggreatest-n-per-group
jonr
jonr

Reputation: 135

Oracle select from table and join where date is oldest

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

Upvotes: 1

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

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;

Upvotes: 2

GMB
GMB

Reputation: 222612

One option uses a lateral join:

select ba.bank_id, ba.client_id, c.name
from client c
cross apply (
    select ba.*
    from bank_account ba
    where ba.client_id = c.client_id
    order by ba.last_used
    fetch first row only
) ba

The upside is that you can easily bring more columns from the bank account tables (say you want the last_used date for example), by just expanding the select clause.

Upvotes: 0

Related Questions