I need to write a query to replace one of the column's values with a value from another row in the same table. I tried to use a simple inner join, but I'm not seeing the results I need.
Here's an example of the table BANK_LOANS:
MONTH_ID LOAN_NUMBER BANK_NAME AMOUNT ======== =========== ========== ======== 76 00-100 Bank One 100.00 75 00-100 The Bank One 150.00 74 00-100 Bank 1 150.00 76 00-200 Another Bank 300.00 75 00-200 Another Bank 500.00
The data is loaded from outside sources which doesn't verify the bank name, so the spelling for a bank name for a given loan number can possibly vary. The customer wants whatever BANK_NAME is in the database for the max MONTH_ID to be used for all the results for that LOAN_NUMBER, and they want the results sorted by BANK_NAME. So for instance, loan 00-100 needs a BANK_NAME of "Bank One" for all rows.
I tried using an inner join to do this, but wasn't getting the correct results
SELECT name.BANK_NAME, bl.LOAN_NUMBER, bl.AMOUNT FROM BANK_LOANS INNER JOIN BANK_LOANS home ON name.LOAN_NUMBER = bl.LOAN_NUMBER AND name.MONTH_ID = 67 --the max id provided to the query ORDER BY name.BANK_NAME, bl.LOAN_NUMBER, bl.MONTH_ID DESC
I think I have it working with an sub-query, but it's kind of ugly. I was wondering if there was a better way (best practices) of accomplishing this with joins or other oracle functions.
This seems to work, but it just feels wrong:
SELECT name.BANK_NAME, bl.LOAN_NUMBER, bl.AMOUNT FROM BANK_LOANS, (SELECT bl2.BANK_NAME, bl2.LOAN_NUMBER FROM BANK_LOANS WHERE bl2.MONTH_ID = 76 --max month id provided to query ) name WHERE name.LOAN_NUMBER = bl.LOAN_NUMBER ORDER BY name.BANK_NAME, bl.LOAN_NUMBER, bl.MONTH_ID DESC
with test_data ( month_id, loan_number, bank_name, amount ) as ( select 76, '00-100', 'Bank One' , 100.00 from dual union all select 75, '00-100', 'The Bank One', 150.00 from dual union all select 74, '00-100', 'Bank 1' , 150.00 from dual union all select 76, '00-200', 'Another Bank', 300.00 from dual union all select 75, '00-200', 'Another Bank', 500.00 from dual ) -- end of test data (not part of the solution). -- SQL query begins BELOW THIS LINE; use with your table. select month_id, loan_number, first_value(bank_name) over (partition by loan_number order by month_id desc) as bank_name, amount from test_data ; MONTH_ID LOAN_N BANK_NAME AMOUNT -------- ------ ------------ ------ 74 00-100 Bank One 150 75 00-100 Bank One 150 76 00-100 Bank One 100 75 00-200 Another Bank 500 76 00-200 Another Bank 300 5 rows selected.