MySQL 5.7

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

create table student (stud varchar (50),   id_prepod int  , counter int)
insert into student values ('иванов',   1111   ,   1),
('иванов'  , 2222    ,  4),
('петров' ,  1111    ,  2 )

Records: 3  Duplicates: 0  Warnings: 0


select * from student


stud
id_prepod
counter


иванов
1111
1

иванов
2222
4

петров
1111
2


select s1.*
from student s1
left join student s2 on s1.stud=s2.stud and s2.counter>s1.counter
where s2.id_prepod is null


stud
id_prepod
counter


иванов
2222
4

петров
1111
2


select s1.stud, s1.id_prepod, s1.counter
from student s1
join (select s2.stud,  max(s2.counter) as counter
from student s2 group by s2.stud) s3 
on s1.stud=s3.stud and  s1.counter=s3.counter


stud
id_prepod
counter


иванов
2222
4

петров
1111
2