purpose: in sql, try to get first row group by a group variable order by the value of a numeric variable.
for example, the data is like: for each business_month, for each borrower, there are multiple date(fac_auth_eff_dt) and different rating.
What I want: for each borrower, I want to get the rating at the first date.
How:
1: for each borrower, add a column called row_number(like order) by their date [row_number() over (partition by borrower_uen order by fac_auth_eff_dt) rn]
2: now for each borrower, row_number = 1, means the earliest fac_auth_eff_dt, row_number = 1 means the second earliest fac_auth_eff_dt and so on.
3: finally pick the data with row_number = 1 to get the first one
with testtable as(
select borrower_uen, fac_auth_eff_dt, row_number() over (partition by borrower_uen order by fac_auth_eff_dt) rn, risk_rating as origination_risk_rating
from bslh.bcs_lending_airb_m
where fac_commitment_amt > 1000 and borrower_rrs = 'CM1' and fac_auth_eff_dt is not null
order by borrower_uen
)
select *
from testtable
where rn = 1