Select with the last record

Question

I need to value the inventory of the company I work for and for that I will take the last value of the items. I was using the MAX, but I realized that if I did this, it would return me the highest price and not the last registered in the table. So I changed the MAX for the date column, only it brings me several rows of results, and I need only the last but still with the code field and value to then play in a spreadsheet.

Code:

SELECT irec.pro_in_codigo Codigo,
       irec.rci_re_vlunitario Valor_unitario,
       max(irec.rcb_dt_documento) Data    
FROM mgadm.est_itensreceb irec    
WHERE irec.pro_in_codigo = 701    
GROUP BY irec.pro_in_codigo, irec.rci_re_vlunitario

Answer 1

See if it works:

select irec.pro_in_codigo Codigo,
       irec.rci_re_vlunitario Valor_unitario,
       irec.rcb_dt_documento Data
from mgadm.est_itensreceb irec
where irec.pro_in_codigo = 701 
    and irec.rcb_dt_documento = (select max(irec.rcb_dt_documento) Data
            from mgadm.est_itensreceb irec
            where irec.pro_in_codigo = 701
            )

Answer 2

In Firebird I do so:

SELECT   FIRST 1
         VALOR_UNITARIO
FROM     ESTOQUE
WHERE    CODIGO = :CODIGO
ORDER BY DATA DESC

Answer 3

Do select as an example below.

SELECT IREC.PRO_IN_CODIGO CODIGO,
   IREC.RCI_RE_VLUNITARIO VALOR_UNITARIO,
   IREC.RCB_DT_DOCUMENTO
FROM MGADM.EST_ITENSRECEB IREC
INNER JOIN (
            SELECT IREC.PRO_IN_CODIGO, /* CAMPOS CHAVES DA TABELA */
                MAX(IREC.RCB_DT_DOCUMENTO) AS MAXDT
            FROM  MGADM.EST_ITENSRECEB IREC
            GROUP BY  IREC.PRO_IN_CODIGO
       ) TV_MAX ON (TV_MAX.PRO_IN_CODIGO = IREC.PRO_IN_CODIGO AND IREC.RCB_DT_DOCUMENTO = TV_MAX.MAXDT)
WHERE IREC.PRO_IN_CODIGO = 701