MySQL 5.6

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

  CREATE TABLE IF NOT EXISTS `students` (
    `uid` int(11) NOT NULL AUTO_INCREMENT,
    `udate` date NOT NULL,
    `oder` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `Subject` varchar(20) NOT NULL,
    `mark` int(11) NOT NULL,
    PRIMARY KEY (`uid`)
  ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


  INSERT INTO `students` (`uid`, `udate`, `oder`, `name`, `Subject`, `mark`) VALUES
  (1, '2021-08-01', 1, 'Mujeeb', 'ENGLISH', 10),
  (2, '2021-08-01', 1, 'Zakariya', 'ENGLISH', 20),
  (3, '2021-08-10', 2, 'Mujeeb', 'ENGLISH', 50),
  (4, '2021-08-11', 2, 'Zakariya', 'ENGLISH', 60),
  (5, '2021-08-02', 1, 'Mujeeb', 'ENGLISH', 100),
  (6, '2021-08-03', 1, 'Zakariya', 'ENGLISH', 110),
  (7, '2021-08-10', 1, 'Mujeeb', 'ENGLISH', 500),
  (8, '2021-08-11', 1, 'Zakariya', 'ENGLISH', 600),
  (9, '2021-08-01', 2, 'Mujeeb', 'MATHS', 100),
  (10, '2021-08-01', 2, 'Zakariya', 'MATHS', 75),
  (11, '2021-08-10', 3, 'Mujeeb', 'MATHS', 50),
  (12, '2021-08-11', 3, 'Zakariya', 'MATHS', 60);
SELECT s1.*
FROM students s1
WHERE NOT EXISTS (
  SELECT 1
  FROM students s2
  WHERE s2.name = s1.name AND s2.Subject = s1.Subject
    AND (s2.udate > s1.udate OR (s2.udate = s1.udate AND s2.oder > s1.oder))  
)


uid
udate
oder
name
Subject
mark


3
2021-08-10
2
Mujeeb
ENGLISH
50

4
2021-08-11
2
Zakariya
ENGLISH
60

11
2021-08-10
3
Mujeeb
MATHS
50

12
2021-08-11
3
Zakariya
MATHS
60


SELECT s1.*
FROM students s1
WHERE s1.uid = (
  SELECT s2.uid
  FROM students s2
  WHERE s2.name = s1.name AND s2.Subject = s1.Subject
  ORDER BY s2.udate DESC, s2.oder DESC LIMIT 1
)


uid
udate
oder
name
Subject
mark


3
2021-08-10
2
Mujeeb
ENGLISH
50

4
2021-08-11
2
Zakariya
ENGLISH
60

11
2021-08-10
3
Mujeeb
MATHS
50

12
2021-08-11
3
Zakariya
MATHS
60

uid	udate	oder	name	Subject	mark
3	2021-08-10	2	Mujeeb	ENGLISH	50
4	2021-08-11	2	Zakariya	ENGLISH	60
11	2021-08-10	3	Mujeeb	MATHS	50
12	2021-08-11	3	Zakariya	MATHS	60