Selecting the most recent records added to a table

[mcmon999]

I'm trying to select the most recent records added to a table. I have three tables (User, user_training, training) user_trainging is resolving the many to many relationship and the table i am querying.

When i run a normal query grouping by the training ID, it selects the first record inserted for the user:

SQL:

SELECT distinct training_id, td_id, level, completed from user_training where td_id = 1090165 group by training_id

Output:
+-------------+---------+--------+---------------------+
| training_id | td_id | level | completed |
+-------------+---------+--------+---------------------+
| 1 | 1090165 | 1 | 2007-11-16 12:15:31 |
| 2 | 1090165 | 4 | 2007-11-16 14:39:20 |
| 6 | 1090165 | 3 | 2007-11-20 15:08:19 |
| 25 | 1090165 | 3 | 2007-11-16 15:06:55 |
+-------------+---------+--------+---------------------+

I was informed that the Max Aggregate would select the last record inserted:

SQL: SELECT distinct training_id, td_id, level, max(completed) from user_training where td_id = 1090165 group by training_id

Output:
+-------------+---------+--------+---------------------+
| training_id | td_id | level | max(completed) |
+-------------+---------+--------+---------------------+
| 1 | 1090165 | 1 | 2007-11-16 15:19:04 |
| 2 | 1090165 | 4 | 2007-11-16 14:39:20 |
| 6 | 1090165 | 3 | 2007-11-20 15:08:19 |
| 25 | 1090165 | 3 | 2007-11-16 15:06:55 |
+-------------+---------+--------+---------------------+

But this selects the wrong data, it selects the correct training_id, td_id and completed date but the level is wrong. For training ID 1 the last completed level was level 2.

Does anyone know why when using this Aggregate it selects some incorrect data?

Could it be the relationship in the tables (I have set training_id, td_id and completed as the PK_ID)

Is there another way to select the last record insert?

Thanks in advance.

James

 

[r937]

SELECT training_id
     , td_id
     , level
     , completed 
  FROM user_training AS ut
 WHERE completed =
       ( SELECT MAX(completed)
           FROM user_training
          WHERE training_id = ut.training_id )
   AND td_id = 1090165

r937.com | rudy.ca

 

[mcmon999]

Excellent thanks very much! Do you know why this happens?