MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable( id 	int , rev int,	revtype int, 	foo_description varchar(100));
insert into mytable values
(10 	,1 	,0 	,'10 foo v1')
,(10 	,3 	,1 	,'10 foo v2')
,(20 	,4 	,1 	,'20 foo v3')
,(20 	,2 	,0 	,'20 foo v1')
,(20 	,4 	,1 	,'20 foo v2');

SELECT * FROM mytable
order by id ,rev;


id
rev
revtype
foo_description


10
1
0
10 foo v1

10
3
1
10 foo v2

20
2
0
20 foo v1

20
4
1
20 foo v3

20
4
1
20 foo v2


select * from mytable t1
where (id,rev) = ( 
     select id, rev from mytable t2
     where t2.rev < 4
     and t1.id = t2.id
     order by rev desc limit 1
     ) 


id
rev
revtype
foo_description


10
3
1
10 foo v2

20
2
0
20 foo v1


select * from (
   select * , row_number() over (partition by id order by rev desc) rn
   from mytable
   where rev < 4
) t where rn = 1


id
rev
revtype
foo_description
rn


10
3
1
10 foo v2
1

20
2
0
20 foo v1
1

id	rev	revtype	foo_description
10	1	0	10 foo v1
10	3	1	10 foo v2
20	2	0	20 foo v1
20	4	1	20 foo v3
20	4	1	20 foo v2