get the last balance of each account. select the last row of each group

get the last balance of each account

MS SQL-way:

select * from
accountinstallmentfinancingdetailfinal detail

inner join (select invoiceno, pmtstructureno, max (paymentsortorder) as lastrow
group by invoiceno, pmtstructureno) as lastrowofaccount

on lastrowofaccount.invoiceno = detail.invoiceno and lastrowofaccount.pmtstructureno = detail.pmtstructureno and lastrowofaccount.lastrow = detail.paymentsortorder;


PostgreSQL way (note: the above would also work on PostgreSQL):

select distinct on (invoiceno, pmtstructureno) *
from accountinstallmentfinancingdetailfinal
order by invoiceno, pmtstructureno, paymentsortorder desc;