CODEWITHSUNDEEP
oracle-database
Patrick
Patrick

Reputation: 2577

Joining the top result in Oracle

I'm using this query: 

SELECT * 
FROM HISTORY
LEFT JOIN CUSTOMER ON CUSTOMER.CUST_NUMBER = HISTORY.CUST_NUMBER
LEFT JOIN ( 
    Select LOAN_DATE, CUST_NUMBER, ACCOUNT_NUMBER, STOCK_NUMBER, LOC_SALE
    From LOAN
    WHERE ACCOUNT_NUMBER != 'DD'
    ORDER BY LOAN_DATE DESC
    ) LOAN ON LOAN.CUST_NUMBER  = HISTORY.CUST_NUMBER
order by DATE desc

But I want only the top result from the loan table to be joined (Most recent by Loan_date). For some reason, it's getting three records (one for each loan on the customer I'm looking at). I'm sure I'm missing something simple?

Upvotes: 1

Views: 31

Answers (1)

Boneist
Boneist

Reputation: 23588

If you're after joining the latest loan row per cust_number, then this ought to do the trick:

select * 
from   history
       left join customer on customer.cust_number = history.cust_number
       left join (select loan_date,
                         cust_number,
                         account_number,
                         stock_number,
                         loc_sale
                  from   (select loan_date,
                                 cust_number,
                                 account_number,
                                 stock_number,
                                 loc_sale,
                                 row_number() over (partition by cust_number
                                                    order by loan_date desc) rn
                          from   loan
                          where account_number != 'DD')
                  where   rn = 1) loan on loan.cust_number  = history.cust_number
order by date desc;

If there are two rows with the same loan_date per cust_number and you want to retrieve both, then change the row_number() analytic function for rank().

If you only want to retreive one row, then you'd have to make sure you add additional columns into the order by, to make sure that the tied rows always display in the same order, otherwise you could find that sometimes you get different rows returned on subsequent runs of the query.

Upvotes: 2

Related Questions