Getting most recent record from table via self join

Question

I have a table with the following schema:

CREATE TABLE IF NOT EXISTS `ATTEMPTS` 
  ( 
     ID         INT NOT NULL AUTO_INCREMENT, 
     USERS_ID   INT, 
     TESTS_ID   INT, 
     SCORE      FLOAT(10, 4), 
     CREATED    DATETIME DEFAULT NULL, 
     MODIFIED   DATETIME DEFAULT NULL, 
     IS_DELETED BIT(1) DEFAULT NULL, 
     PRIMARY KEY(ID) 
  );

A user can have multiple attempts for different tests. I am trying to think through the best way to return all of the most recent test attempts for each user. So if user A has two attempts for test 8 and three for test 17, while user B has one attempt for test 8 then the returned table would have 3 records: two from user A and one from user B. These records being the respective most recent test attempts for each test type taken.

I think a self join is needed for this, but I'm not sure. It there a way around using a self join? I'm not sure what is the best solution to return what I want from this table. I cannot think of a way to filter by "most recent" given the organization of the data. Basically, I'm not sure how to build this query.

This is my (NOT YET WORKING) query thus far:

SELECT a.USER_ID, 
       a.TEST_ID, 
       a.SCORE, 
       a.MODIFIED 
FROM   ATTEMPTS AS a, 
       ATTEMPTS AS b 
WHERE  a.USER_ID = b.USER_ID 
       AND  (Some clause to deal with most recent?)

Thank you to anyone who can help.

possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Barmar, Sep 02 '13 at 16:26
I'm reviewing this. Thanks guys. This question it a duplicate. I failed to realize that created could be governed by a max statement. — usumoio, Sep 02 '13 at 17:24

score 2 · Accepted Answer · answered Sep 02 '13 at 16:26

This should do:

SELECT A.*
FROM `attempts` A
INNER JOIN (SELECT USERS_ID, TESTS_ID, MAX(CREATED) MaxCreated
            FROM `attempts`
            GROUP BY USERS_ID, TESTS_ID) B
    ON A.USERS_ID = B.USERS_ID
    AND A.TESTS_ID = B.TESTS_ID
    AND A.CREATED = B.MaxCreated

Or:

SELECT A.*
FROM `attempts` A
WHERE EXISTS (SELECT 1
              FROM (SELECT USERS_ID, TESTS_ID, MAX(CREATED) MaxCreated
                    FROM `attempts`
                    GROUP BY USERS_ID, TESTS_ID) X
              WHERE X.USERS_ID = A.USERS_ID
              AND X.TESTS_ID = A.TESTS_ID
              AND X.MaxCreated = A.CREATED)

It looks like I did a bad job of explaining myself and also that what I needed was easier than I thought. All I needed was the interior part of your query, `SELECT user_id, test_id, MAX(modified) max_mod FROM attempts GROUP BY user_id, test_id` — usumoio, Sep 02 '13 at 18:15

Getting most recent record from table via self join

1 Answers1