MYSQL 5.6 get latest data of each user

Solution for MYSQL 5.6 get latest data of each user
is Given Below:

My Database table is as shown below. I need to get latest mark of each student. Latest entry is the row with maximum udate and maximum oder. (The oder will be incremented by one on each entry with same date)

enter image description here

In my example, I have two students Mujeeb, Zakariya and two subjects ENGLISH, MATHS. I need to get latest mark of each student for each subject. My expectd result is as follows

enter image description here

My sample data is

  DROP TABLE IF EXISTS `students`;
  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);    

Use NOT EXISTS:

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))  
);

Or with a correlated subquery in the WHERE clause:

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
);

See the demo.

As ROW_NUMBER() function doesn’t work at lower version of MySQL, So alternate way of row_number() is used for this solution.

-- MySQL (v5.6)
SELECT p.uid, p.udate, p.oder, p.name, p.Subject, p.mark
FROM (SELECT @row_no := IF((@prev_val = t.name && @prev_val1 = t.Subject), @row_no + 1, 1) AS row_number
                 , @prev_val := t.name AS name
                 , @prev_val1 := t.Subject AS Subject
                 , t.mark
                 , t.oder
                 , t.uid
                 , t.udate
            FROM students t,
            (SELECT @row_no := 0) x,
            (SELECT @prev_val := '') y,
            (SELECT @prev_val1 := '') z
            ORDER BY t.name, t.Subject, t.udate DESC, t.oder DESC ) p
WHERE p.row_number = 1
ORDER BY p.name, p.Subject;

Please check the url http://sqlfiddle.com/#!9/b5befe/18