Firebird 4.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


4.0.1
UTF8


create table agdatum(
  artnr integer,
  nr integer,
  typ integer,
  stueckg integer,
  datum date
);
execute block as
begin
  insert into agdatum(artnr, nr, typ, stueckg, datum) values (1, 1, 2, 100, date '01.01.2022');
  insert into agdatum(artnr, nr, typ, stueckg, datum) values (1, 1, 0, 0, date '01.01.2022');
  insert into agdatum(artnr, nr, typ, stueckg, datum) values (1, 1, 2, 22, date '05.01.2022');
  insert into agdatum(artnr, nr, typ, stueckg, datum) values (1, 2, 2, 33, date '05.01.2022');
  insert into agdatum(artnr, nr, typ, stueckg, datum) values (2, 1, 0, 0, date '06.01.2022');
end
5 rows affected

with cte as(
  select artnr, nr, typ, max(datum) as dt 
  from agdatum 
  where typ > 0 
  group by artnr, nr, typ
)
select artnr, nr, typ, 
  (select stueckg 
   from agdatum 
   where artnr = cte.artnr and nr = cte.nr and typ = cte.typ and datum = cte.dt) 
from cte


ARTNR
NR
TYP
STUECKG


1
1
2
22

1
2
2
33


select artnr, nr, typ, stueckg
from (
  select artnr, nr, typ, stueckg, 
    row_number() over(partition by artnr, nr order by datum desc) as rn
  from agdatum
  where typ > 0
)
where rn = 1


ARTNR
NR
TYP
STUECKG


1
1
2
22

1
2
2
33


with cte as(
  select artnr, nr, typ, max(datum) as dt 
  from agdatum 
  where typ > 0 
  group by artnr, nr, typ
)
select a.artnr, a.nr, a.typ, a.stueckg
from cte 
cross join lateral (
  select artnr, nr, typ, stueckg
  from agdatum
  where artnr = cte.artnr and nr = cte.nr and typ = cte.typ and datum = cte.dt
) a


ARTNR
NR
TYP
STUECKG


1
1
2
22

1
2
2
33


with cte as(
  select artnr, nr, typ, max(datum) as dt 
  from agdatum 
  where typ > 0 
  group by artnr, nr, typ
)
select a.artnr, a.nr, a.typ, a.stueckg
from cte 
inner join agdatum a
  on a.artnr = cte.artnr and a.nr = cte.nr and a.typ = cte.typ and a.datum = cte.dt


ARTNR
NR
TYP
STUECKG


1
1
2
22

1
2
2
33