How can i select the most recent purchase by the Clients?

Question

In a database with several tables and relationships between them, I want to select only the most recent payment by the clients last year.

My query goes like this:

SELECT
  P.Name,
  EV.EventName,
  FN.Installments,
  FN.PurchaseValue,
  FN.DueDate

FROM ClientPrivate PF
JOIN Client P        ON P.PesControle = PF.PesControle
JOIN ClientClass CP  ON P.PesControle = CP.PesControle
JOIN EVENT EV        ON CP.EveControle = EV.EveControle 
JOIN Class cc        ON cc.CurControle = EV.CurControle   
JOIN Finance FN      ON FN.PesControle = P.PesControle 

It returns the values I need, only I'd like to get only the most recent purchase by each client, instead of all of them.

I edited to help clarify. The 'Controle' columns are the keys.

Answer 1

Whatever you date column is put that in the ORDER BY Clause of the ROW_NUMBER() function and you are good to go.

;WITH CTE AS
 (
 SELECT
  P.PesNome,
  EV.EveDescri,
  FN.FinTotParc,
  FN.FinVlrLiquido,
  FN.FinDiaVencto,
  ROW_NUMBER() OVER (PARTITION BY P.PesNome ORDER BY [DateColumn] DESC) rn

FROM PessoaFisica PF
JOIN Pessoa P        ON P.PesControle = PF.PesControle
JOIN CursoPessoa CP  ON P.PesControle = CP.PesControle
JOIN EVENTO EV       ON CP.EveControle = EV.EveControle 
JOIN Curso cc        ON cc.CurControle = EV.CurControle   
JOIN Financeiro FN   ON FN.PesControle = P.PesControle 
  )
SELECT * 
FROM CTE 
WHERE rn = 1