How to take the last sale of each customer

Question

I have a client table:

----------------------------------------------------
codigocliente || razao        || cidade   || estado
----------------------------------------------------
0001          || Sup. Mercado || Londrina || PR 
----------------------------------------------------
0002          || Super Pão    || Arapongas|| PR
----------------------------------------------------
0003          || Loja Comer.  || Cambé    || PR
----------------------------------------------------

and a bill of sale table:

----------------------------------------------------
n_nota || valor_nota || dt_emissao || codigocliente
----------------------------------------------------
0001   || 1000,00    || 2021-01-01 || 0001
----------------------------------------------------
0002   || 2500,00    || 2021-02-13 || 0002  
----------------------------------------------------
0003   || 800,00     || 2021-02-27 || 0001
----------------------------------------------------
0004   || 1500,00    || 2021-03-20 || 0003 
----------------------------------------------------

SELECT
    c.codigocliente, 
    c.razao, 
    c.cidade, 
    c.estado, 
    MAX(nf.dt_emissao),
    nf.valor_nota
FROM clientes c
LEFT JOIN nota_fiscal nf ON nf.codigocliente = c.codigocliente
GROUP BY c.codigocliente;

How to take the last purchase amount for the last date?

Answer 1

To do this you can break it into two queries.

The first will return the n_note of the latest date grouped by client:

SELECT MAX(n_nota) n_nota 
  FROM nota_fiscal nf1
 GROUP BY codigocliente

Then just add this subquery and do the JOIN of nota_fiscal with this query, to ensure that it takes the data from the correct nota_fiscal:

SELECT
    c.codigocliente, 
    c.razao, 
    c.cidade, 
    c.estado, 
    nf.dt_emissao,
    nf.valor_nota
FROM clientes c
LEFT JOIN nota_fiscal nf ON nf.codigocliente = c.codigocliente
INNER JOIN 
    (SELECT MAX(n_nota) n_nota 
       FROM nota_fiscal nf1
      GROUP BY codigocliente) AS aux
      ON nf.n_nota = aux.n_nota
GROUP BY c.codigocliente;

Note that in the SELECT main, is taking directly the date and value, this is because the last JOIN (nf.n_nota = aux.n_nota) ensures that the record in "nota_fiscal" is the most current, which is the n_note returned in the subquery.

You can see it working here: http://sqlfiddle.com/#! 9/541c882/11

UPDATE: after comments, when testing the above query to bring customers without sale, when replacing the INNER JOIN for LEFT JOIN returns customer data, but does not bring correct record of sale, because of SELECT which calculates the MAX(n_nota). To solve this, I added another level in the query, to the LEFT JOIN with "customers" being the one JOIN in the "first level" of the query, thus:

SELECT
    c.codigocliente, 
    c.razao, 
    c.cidade, 
    c.estado, 
    nf.dt_emissao,
    nf.valor_nota
FROM clientes c
LEFT JOIN (
  -- dados da nf mais recente
  SELECT codigocliente,
         dt_emissao, 
         valor_nota
   FROM nota_fiscal nf1
  INNER JOIN 
        -- nf mais recente
        (SELECT MAX(n_nota) n_nota 
           FROM nota_fiscal
          GROUP BY codigocliente) AS aux ON nf1.n_nota = aux.n_nota
        ) AS nf ON nf.codigocliente = c.codigocliente
GROUP BY c.codigocliente;