0

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

8
  • Your query seems correct. It will show all teachers and their last visit. What is the issue? – ypercubeᵀᴹ Nov 26 '14 at 13:09
  • 1
    It may show though more than 1 visit for teachers that have null in their visit_time. – ypercubeᵀᴹ Nov 26 '14 at 13:10
  • The issue is that running the query I got different result (number of teachers; cirka 20 more) than when using group by teacher. Interesting is that when turning the < to > I got different amount of teachers too. (cirka 15 more). – chejnik Nov 26 '14 at 13:24
  • 1
    It would be easier with some sample data. The queries, as you have explained, seem to work well (the amount must vary if you change < to > because is a cartesian product), first try checking SELECT i1.* FROM visits AS i1 LEFT JOIN visits AS i2 ON i1.teacher = i2.teacher – Gerardo Charles Rojas Vega Nov 26 '14 at 14:19
  • 1
    Another reason for getting more results is that some teachers have more than 1 row with the same (maximum) visit_time. – ypercubeᵀᴹ Nov 26 '14 at 14:35
1

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 !!!

0

Is the FIELD1 supposed to be a primary key/unique column? If so, albeit it might not be the fanciest solution, you could achieve what you are trying to achieve with a subquery. I usually work with T-SQL, so I'm not 100% sure of the syntax, but I ran it through a checker so hopefully it's correct.

SELECT i1.* FROM visits AS i1
WHERE i1.Field1 = 
    (SELECT  i2.Field1 FROM visits AS i2
     WHERE i2.Teacher = i1.Teacher AND i2.visit_time is not null 
     LIMIT 1 
     ORDER BY i2.visit_time DESCENDING)

If the FIELD1 column is not a key, you could switch it out to the visit_time field. It will be slower but the result should be the same.

I am uncertain how to interpret visit_time null. Since the table is named visits there should be no rows with visit_time null, right? If they haven't visited at a point in time, they should logically not have visited at all, right?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.