I have this query
SELECT
user_id,
played_level,
sum( time_taken ) AS time_take
FROM answer
WHERE completed =1
GROUP BY played_level, user_id
ORDER BY time_take
LIMIT 20
This query shows all user id who took minimum time.
But now I want to display only distict user id with his minimum time.
user_id played_level time_take
1 18 19
1 12 21
2 3 25
6 3 26
2 2 27
6 4 27
1 8 32
expected output:
user_id played_level time_taken
1 18 19
2 3 25
6 3 26
First select all distict user_id from table
$distinct_users = "SELECT DISTINCT user_id FROM answer where addeddate BETWEEN :currdate1 AND :currdate2 AND completed=1";
$distinct_users_data = $conn->prepare($distinct_users);
$distinct_users_data->execute(array(':currdate1'=>$prev_date,':currdate2'=>$currdate2));
$distinct_users_arr = $distinct_users_data->fetchAll();
Now find the minimum time of this user_id and store it in array
foreach($distinct_users_arr as $distict_data)
{
$user_ids=$distict_data['user_id'];
$all_user_time = "SELECT user_id, played_level, sum( time_taken ) AS time_take FROM answer where addeddate BETWEEN :currdate1 AND :currdate2
AND completed=1 AND user_id=:user_id GROUP BY played_level, user_id ORDER BY time_take limit 1";
$all_user_time_data = $conn->prepare($all_user_time);
$all_user_time_data->execute(array(':currdate1'=>$prev_date,':currdate2'=>$currdate2,':user_id'=>$user_ids));
$all_user_time_arr = $all_user_time_data->fetchAll();
$all_count= $all_user_time_data->rowCount();
foreach($all_user_time_arr as $leading_user)
{
$new_user_id= $leading_user['user_id'];
$new_time= $leading_user['time_take'];
$leader_board[] = array(
'user_id'=>$new_user_id,
'time_taken'=>$new_time
);
}
}
At end sort that array order by time taken. We get final result
foreach ($leader_board as $key => $row)
{
$sorting[$key] = $row['time_taken'];
}
array_multisort($sorting, SORT_ASC, $leader_board);