I would like to group the teachers by the last visit and show the other values corresponding to the last visit.
CREATE TABLE IF NOT EXISTS `visits` ( `FIELD1` int(5) NOT NULL, `teacher` varchar(9) DEFAULT NULL, `visit_time` int(5) DEFAULT NULL, `1A` varchar(1) DEFAULT NULL, `2A` varchar(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
I used this query
SELECT i1.* FROM visits AS i1 LEFT JOIN visits AS i2 ON (i1.teacher = i2.teacher AND i1.visit_time < i2.visit_time) WHERE i2.visit_time IS NULL
The query in general gives correct results, but in the end there are more teachers than expected (for example when using group by teacher).