Complicated join and MAX() query in MySQL

Question

I need to make a join across 4 tables whilst picking the maximum (i.e. most recent) timestamp of test to associate with a person. For each student in a class, I want to lookup what their most recent test is, and get its ID and timestamp

SELECT students.ref, 
       students.fname, 
       students.sname, 
       classes.name AS 'group', 
       tests.id, 
       max(tests.timestamp)
FROM tests, students, classlinks, classes 
WHERE tests.ref=students.ref AND 
       classlinks.ref=students.ref AND 
       classlinks.classid=29 AND 
       tests.grade=2 AND 
       tests.subject=2 
GROUP BY students.ref
ORDER BY students.sname ASC, students.fname ASC

looks like it is perfect: for each student in a class, it gives the timestamp of their most recent test. Unfortunately, the test ID associated with that timestamp is wrong: it is just giving the test ID of a random test.

If I change the 'group by' to be

GROUP BY students.ref, tests.id

then the query matches correct test IDs to correct timestamps, but now there are several entries for each student. Does anyone have any advice so that I can get one row for each student, with correct test ID matched to correct most recent timestamp? Any help appreciated. Thanks.

Table descriptions:

mysql> describe students;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| ref    | varchar(50) | NO   | UNI | NULL    |                |
| fname  | varchar(22) | NO   |     | NULL    |                |
| sname  | varchar(22) | NO   |     | NULL    |                |
| school | int(11)     | NO   |     | NULL    |                |
| year   | int(11)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe classes;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| subject | int(11)     | YES  | MUL | NULL    |                |
| type    | int(11)     | YES  |     | 1       |                |
| school  | int(11)     | YES  |     | NULL    |                |
| year    | int(11)     | YES  |     | NULL    |                |
| name    | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> describe classlinks;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| ref     | varchar(50) | YES  | MUL | NULL    |                |
| subject | int(11)     | YES  |     | NULL    |                |
| school  | int(11)     | YES  |     | NULL    |                |
| classid | int(11)     | YES  | MUL | NULL    |                |
| type    | int(11)     | YES  |     | 1       |                |
+---------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe tests;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
+------------+-------------+------+-----+-------------------+-----------------------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment              |
| subject    | int(11)     | YES  |     | NULL              |                             |
| ref        | varchar(22) | NO   | MUL | NULL              |                             |
| test       | int(3)      | NO   |     | NULL              |                             |
| grade      | varchar(22) | NO   |     | NULL              |                             |
| timestamp  | timestamp   | NO   | MUL | CURRENT_TIMESTAMP |                             |
| lastupdate | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

Answer 1

Using the logic in SQL the query can be written as follows, not sure about mySQL but hope the logic works.

Select ref
      ,fname
      ,sname
      ,ID
      ,group
      ,Timestamp

From
    (select
        S.ref
        ,S.fname
        ,S.sname,
        ,T.id
        ,classes.name AS 'group'
        ,T.timestamp
        from
            tests T,students S, classlinks, classes
        Where
            T.ref=S.ref and
            T.grade=2 AND 
            classlinks.ref=students.ref AND 
            classlinks.classid=29 AND 
            classlinks.classid=classes.id AND
            T.subject=2 ) A

    inner join

    (SELECT tests.ref        
            ,max(tests.timestamp) 
     FROM 
         tests 
     group by
         tests.ref
    ) B
    on
      A.ref=b.ref and
      A.timestamp = b.timestamp

Answer 2

I am assuming that the combination of (ref,timestamp) is unique in tests table. Here is my solution but I don't have any of your sample data to verify it. If it is incorrect than post a sample data so that I can test it.

UPDATE

Here is the update query which is working check the sqlfiddle

SELECT students.ref, 
  students.fname, 
  students.sname, 
  classes.name AS 'group', 
  tests.id, 
  T.timestamp
FROM (select ref,max(timestamp) as timestamp from tests group by ref)as T
    natural join tests, students, classlinks, classes 
WHERE
 T.ref=students.ref AND 
 classlinks.ref=students.ref AND
 classlinks.classid=classes.id AND 
 classlinks.classid=29 AND 
 tests.grade=2 AND 
 tests.subject=2
ORDER BY students.sname ASC, students.fname ASC