Select with last record

4

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

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
    
asked by anonymous 04.01.2017 / 15:00

3 answers

1

See if this 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
            )
    
02.06.2017 / 15:24
0

Please select the 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
    
04.01.2017 / 16:57
0

In firebird I do this:

SELECT   FIRST 1
         VALOR_UNITARIO
FROM     ESTOQUE
WHERE    CODIGO = :CODIGO
ORDER BY DATA DESC
    
09.03.2017 / 15:55