Return last entry of each product

Question

help me. I’m in a situation where I want to bring only the last note entry of each product to create a report. I created a select, but there are products repeating where they shouldn’t. See select:

SELECT P.CodProd, MAX(E.Entrada) Entrada, P.PrecoCustoUNSC,IE.ValorCaixa 
FROM Entradas E 
        INNER JOIN ItensEntrada IE ON E.Cod_Entrada=IE.Cod_Entrada 
        INNER JOIN Produtos P ON IE.CodProd=P.CodProd 
        WHERE IE.ValorCaixa>0 
GROUP BY P.CodProd, P.PrecoCustoUNSC, IE.ValorCaixa 
ORDER BY 1

---

Upshot:

CodProd Entrada                 PrecoCustoUNSC                          ValorCaixa
------- ----------------------- --------------------------------------- ---------------------------------------
000001  2019-08-20 00:00:00.000 3.0000000000                            3.0000000000
000001  2019-08-19 00:00:00.000 3.0000000000                            10.0000000000
000001  2019-08-23 00:00:00.000 3.0000000000                            15.0000000000
000002  2019-08-20 00:00:00.000 15.0000000000                           15.0000000000
000002  2019-08-23 00:00:00.000 15.0000000000                           65.0000000000
000003  2019-08-23 00:00:00.000 30.0000000000                           30.0000000000
000003  2019-09-06 00:00:00.000 30.0000000000                           50.0000000000
000004  2019-08-23 00:00:00.000 50.0000000000                           50.0000000000
000005  2019-09-04 00:00:00.000 5.0000000000                            5.0000000000
000006  2019-09-04 00:00:00.000 5.0000000000                            5.0000000000
000007  2019-09-04 00:00:00.000 30.0000000000                           30.0000000000
000008  2019-09-04 00:00:00.000 50.0000000000                           50.0000000000
000009  2019-09-05 00:00:00.000 69.0000000000                           69.0000000000
000010  2019-09-05 00:00:00.000 35.0000000000                           35.0000000000
000011  2019-09-11 00:00:00.000 50.0000000000                           50.0000000000
000011  2019-09-06 00:00:00.000 50.0000000000                           60.0000000000
000012  2019-09-11 00:00:00.000 30.0000000000                           30.0000000000
000013  2019-09-11 00:00:00.000 15.0000000000                           15.0000000000
000014  2019-09-11 00:00:00.000 30.0000000000                           30.0000000000
000015  2019-09-11 00:00:00.000 13.0000000000                           13.0000000000
000015  2019-09-11 00:00:00.000 13.0000000000                           6.5000000000
000016  2019-09-11 00:00:00.000 30.0000000000                           30.0000000000

So, product 1 for example is repeating itself, and I wanted only the last record (Input = '2019-08-23') which was the last entry of his product. I needed to relate because in Entries is the date of entry, the Itensentrada contains Valorcaixa that is the cost price where I want to compare with the registration of the product that is Precocustounsc.

Answer 1

Considering the additional information posted in the comments by the author of the topic - we can use instead of input the COD_ENTRADA field which is the sequential number of each note -, here is suggestion:

-- código #1 v2
with Ultimo as (
SELECT P.CodProd, E.Entrada, P.PrecoCustoUNSC, IE.ValorCaixa,
       seq= row_number() over (partition by P.CodProd order by E.Cod_Entrada desc)
  from Entradas E 
       inner join ItensEntrada as IE on E.Cod_Entrada = IE.Cod_Entrada 
       inner join Produtos as P on IE.CodProd = P.CodProd 
  where IE.ValorCaixa > 0 
)
SELECT CodProd, Entrada, PrecoCustoUNSC, ValorCaixa
  from Ultimo
  where seq = 1
  order by CodProd;

---

In CTE Ultimo for each product a sequential numerator is generated (seq), starting from 1 to the latest (i.e., with higher Cod_input value) and incrementing as it is older. To follow this step just run the following code:

-- código #2
SELECT P.CodProd, E.Entrada, P.PrecoCustoUNSC, IE.ValorCaixa,
       seq= row_number() over (partition by P.CodProd order by E.Cod_Entrada desc)
  from Entradas E 
      inner join ItensEntrada as IE on E.Cod_Entrada = IE.Cod_Entrada 
      inner join Produtos as P on IE.CodProd = P.CodProd 
  where IE.ValorCaixa > 0
  order by P.CodProd, E.Cod_Entrada desc;

After, just read the result of Ultimo, filtering by those whose seq is 1.

Answer 2

This task may be a little more complex than it looks at first glance, so look, I’m going to put a code here just for you to do a test, I’m not a DBA experienced enough to say if this is the best technique. I also don’t know which field name is the primary key of your table ItensEntrada then you correct yourself there.

SELECT
    ie.CodProd,
    e.Entrada,
    p.PrecoCustoUNSC,
    ie.ValorCaixa
FROM (
    SELECT
        i1.CodProd,
        i1.Cod_Entrada,
        i1.ValorCaixa
    FROM ItensEntrada i1
    WHERE
        i1.CodItemEntrada = (SELECT MAX(i2.CodItemEntrada) FROM ItensEntrada i2 WHERE i2.CodProd = i1.CodProd)
) ie
INNER JOIN produtos p ON p.CodProd = ie.CodProd
INNER JOIN Entradas e ON e.Cod_Entrada = ie.Cod_Entrada
ORDER BY ie.CodProd