<!-- --> > 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); > > <pre> > ✓ > > ✓ > </pre> <!-- --> > 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)) > ) > > <pre> > 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 > </pre> <!-- --> > 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 > ) > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=6f536a9095d26c71939df562ae683c84)*