Summing Only The Latest Values for each Id

Question

I have a table with this data.

For each unique id I would like to sum the Principle column where the date is the most recent for that id (the most recent date for one id may not be the most recent date for another id). So in this example, I would like to sum row 1 (67,000) and row 4 (100,500).

I was going down the route of using cursors but thought there must be a better way. Can someone point me in the right direction?

Answer 1

One option uses a subquery for filtering:

select t.*
from mytable t
where t.paymentDate = (
    select max(t1.paymentDate)
    from mytable t1
    where t1.id = t.id
)