Hi,
I have a table tbl_usertests from which i want to retrieve the user who have maximum testscore for each test.
Note: User here means usertestid which is unique.
Its colums are:
pk_usertestid attemptdate uploaddate fk_tbl_tests_testid fk_tbl_users_userid testscore totalquestionsnotattempted totalquestionscorrect totalquestionsincorrect totalquestions timetaken iscurrent
data :
1;NULL;"2010-06-24 22:48:07";"11";"3";"1";"53";"1";"21";"75";"92";"1"
2;NULL;"2010-06-25 01:21:37";"11";"4";"13";"0";"13";"62";"75";"801";"1"
3;NULL;"2010-06-25 01:21:50";"10";"4";"17";"5";"17";"53";"75";"640";"1"
4;NULL;"2010-06-25 01:24:23";"11";"4";"13";"0";"13";"62";"75";"801";"1"
5;NULL;"2010-06-25 01:24:47";"10";"4";"17";"5";"17";"53";"75";"640";"1"
6;NULL;"2010-06-25 01:36:04";"11";"5";"13";"0";"13";"62";"75";"801";"1"
7;NULL;"2010-06-25 01:47:26";"7";"5";"10";"1";"10";"49";"60";"302";"1"
My Query is :
SELECT max(`testscore`) , `fk_tbl_tests_testid` , `fk_tbl_users_userid` , `pk_usertestid`
FROM `tbl_usertests`
GROUP BY `fk_tbl_tests_testid`
This query output:
max(`testscore`) fk_tbl_tests_testid fk_tbl_users_userid pk_usertestid
10 7 5 7
17 10 4 3
13 11 3 1
But the problem is that if there are two users who have same score, it displays only one user because i have used group by clause.
For. e.g. testid =10 i have two records(pk_usertestid 3 and 5) but it displays 3 only.
I want the user whose upload date is less than the other user(in case of two users having same testscore). It should display for usertestid=3 since 3 upload date is less than 5. Right now its displaying 3 but it is due to group by clause.
I am unable to construct the query.
Please help me on this
Thanks