How can i get rows of the last "datetime" added for each user depends on group id, then from that results find the max "right_answers" from all users and finally if two user equal then get the low "timesec" value
Data table:
- - - - - - -
Groupid | Name | date_time | right_answers | timesec
1 | Fadi | 2020-11-24 11:04:00 | 1 | 10
1 | Jake | 2020-11-24 11:14:00 | 9 | 70
1 | Fadi | 2020-11-24 11:24:00 | 5 | 40
1 | Jake | 2020-11-24 11:34:00 | 7 | 50
1 | Jake | 2020-11-24 11:54:00 | 5 | 35
2 | Bob | 2020-11-24 11:59:00 | 8 | 45
Desired result:
- - - - - - - - -
Name | date_time | right_answers | timesec
Jake | 2020-11-24 11:30:00 | 5 | 35
Fadi | 2020-11-24 11:54:00 | 5 | 40
the result depends on:
- the last date time added (no matter what the count of the right answers is)
- the max right answers
- if equal with other user then order by timesec
timesec = time in seconds for resolving the puzzle
I tried like this but, it's give me the first datetime:
$Sql = "SELECT users.firstname, users.fathername, users.lastname,
scores.user_id,scores.right_answers,scores.timesec, scores.user_id
FROM scores
INNER JOIN users ON scores.user_id=users.id
WHERE scores.group_id='$groupID'
GROUP BY scores.user_id
ORDER BY scores.date_time ASC, scores.right_answers DESC, scores.timesec ASC";
in this case: I've got the resutl with names repeating (like fadi repeats 3 times and Jake 3 times)
$Sql = "SELECT users.firstname, users.fathername, users.lastname,
scores.user_id,scores.right_answers,scores.timesec, scores.user_id
FROM scores
INNER JOIN users ON scores.user_id=users.id
WHERE scores.group_id='$groupID'
ORDER BY scores.right_answers DESC, scores.timesec ASC";
Thank you ...
