Last table record

[gab26]

Good morning everyone, I'm new here, I'm looking for a help I can not solve I hope someone can help me.

I need to value the company inventory in which I work, for this I will get the last value of the items, I was using Max, but I realized that if I did this he would return me the highest price and not the last one registered in the table so I changed the max for the date column, but it brings me several lines of results, and I need only the last but still with the code field and value of the items, and then play in a spreadsheet, I will post The Code here below

Select all

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 

Please ask if you can give me a light

thanks.

[spernega]

Good afternoon,

see if it helps

Select all

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

provided that he has no further On the same day for product, or that the date is datetime.

[gab26]

Good afternoon,

see if it helps



provided no more than A release on the same day for product, or that the date is datetime.

spernega, good afternoon

Dude thank you very much helped me , I did not imagine it was so simple and that it was going to be answered so fast.

[gab26]

Spernega, good afternoon

Only one thing I need several items at the same time, I did not know that it was going to change the end result,
but for what I could observe to Items, it brings only the item with the last date that would be today.

Can you help me?

Thanks.

[spernega]

Clear, see if it is certain

Select all

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

[gab26]

Thank you so much solved the problem

[Ericke_Cruz]

Spernega, I do not know if it's still out there ... but I set up the code exactly the same way you'd guided, but you're returning the following error:

ORA-00907: parenthesis Law not found

Below follows the code:



When the right parenthesis of place is changed, As below, he brings me some result, but still is not what I need, because in this case he seeks the last date of movement and returns only what moved on that date, I needed him to bring the last movement of each independent code On which date it occurred.

Select all

SELECT * 
  FROM E210MVP A 
 WHERE A.DATMOV = (SELECT MAX(DATMOV) 
                     FROM E210MVP) 
                      AND CODPRO = A.CODPRO 
   AND A.ESTMOV = 'NO' 
   AND A.FILDEP = '201'

I apologize if I did not make me understand, first post ...

[spernega]

Good afternoon, so here, still, half away but on the active.
tries to place an alias in the sub-query

Select all

SELECT * 
  FROM E210MVP A 
WHERE A.DATMOV = (SELECT MAX(b.DATMOV) 
                     FROM E210MVP b 
                      AND b.CODPRO = A.CODPRO) 
   AND A.ESTMOV = 'NO' 
   AND A.FILDEP = '201'

[DanielNN]

Good afternoon,
The problem is that in your subquery there is no clause where . There is a and without wher .

[Ericke_Cruz]

Good afternoon,
The problem is that in your subquery there is no clause where . There is a and without the where .

I really had not noticed ... Thanks !!!