clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805502 fiddles created (40755 in the last week).

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);
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)