I have a table called customer_access that keeps track of what users have access to what customer.
I need to get a list of customer_access records that would give me the list of records where the approval_date is the latest for each of the user-customer combinations.
Currently, I am doing it this way, but I have a feeling that it can be done more elegantly. I am looking to improve the clarity of the sql w/o hurting the performance (or, ideally, improving performance as well).
select * from (select row_number() over (partition by ca.customerId, ca.userId ORDER BY ca.approvalDate DESC) as rownumber, ca.id from customer_access ca) ca1 where ca1.rownumber = 1