1

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);
Sam
  • 131
  • 1
  • 12
  • I have found solution @ [https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql). But in my case how to do it? – Sam Aug 02 '21 at 08:25
  • Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – Nico Haase Aug 02 '21 at 08:31
  • No, a slight difference. In that post, latest entry determined by id, but in my case it is with both `udate` and `oder`. – Sam Aug 02 '21 at 11:39

2 Answers2

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • As I have over 50MB data, which would be more efficient? – Sam Aug 02 '21 at 11:30
  • @Sam usually EXISTS performs better because it returns as soon as it finds a match. – forpas Aug 02 '21 at 12:26
  • In first example what happening `SELECT 1`. pls give me some explanation – Sam Aug 03 '21 at 06:40
  • @Sam `1` is not significant. It could be `0` or `*` or even `null`. What is important here is that SELECT inside EXISTS returns or not a row. You can find more about it here: https://www.mysqltutorial.org/mysql-exists/ – forpas Aug 03 '21 at 06:47
  • @Sam This is the condition for the max udate and if there are more than 1 max udates then get the max oder. – forpas Aug 05 '21 at 10:34
1

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

Rahul Biswas
  • 3,207
  • 2
  • 10
  • 20