Find the last visit by teacher and show the rest of columns in MySql 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 &lt; 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).
You need to join all the teacher's last visit_time value against the whole visits table

SELECT B.* FROM
(
    SELECT teacher,MAX(visit_time) visit_time
    FROM visits GROUP BY teacher
) A INNER JOIN visits B USING (teacher,visit_time);


Based on this query, you should also add the following index

ALTER TABLE visits ADD INDEX teacher_visit_time_ndx (teacher,visit_time);


Give it a Try !!!