Adam Adam - 5 months ago 11
MySQL Question

Select the last date from multiple rows in mysql

So i have a small issue:
I have users who can enter a test multiple times. I want to inner join the users table with the results of the last test they took.

TABLES: USERS, TESTRESULTS

I'm kinda stuck on the syntax, got it like this right now

SELECT USERS.*, TESTRESULTS.result
FROM USERS
INNER JOIN TESTRESULTS
ON USERS.id = TESTRESULTS.user_id
WHERE TESTRESULTS.date IN (SELECT id, MAX(date) FROM TESTRESULTS GROUP BY id)


What I would like to achieve is this:

user_id, (latest result for test)
1, 98
2, 77
3, 100


The users can take as many tests as they like, but only the last one will count.

Help would be really appreciated :)

Answer

You were missing a little something in the IN() statement :

SELECT USERS.*, TESTRESULTS.result
FROM USERS
INNER JOIN TESTRESULTS
ON USERS.id = TESTRESULTS.user_id
WHERE (users.id,TESTRESULTS.date) IN (SELECT id, MAX(date) FROM TESTRESULTS GROUP BY id)

You have to compare the same number of arguments and the same type when using IN() .

Comments