Get row for a list of id's where a specific field is max in a MySQL database

Question

I have the following tables in a MySQL database:

routepoints:

   routepoint_id | route_id |    latitude    |     longitude     | location | routepoint_time
_________________|__________|________________|___________________|__________|__________________
        24       |   38     |   9.32454363   |    10.65785453    |   blah   |     1232455678
        25       |   38     |  56.72112300   |    11.40735700    |   blah   |  3324546454363
        26       |   39     |  12.83276562   |    12.83276562    |   blah   |    54321325541
        27       |   39     |  52.72112300   |    10.23400000    |   blah   |    64321325541     
_________________|__________|________________|___________________|__________|__________________
                 |          |                |                   |          |

And routes

   route_id    |  user_id  | creation_date  
_______________|___________|________________
      38       |    18     |    1243256576   
      39       |    16     | 3463468576543 
_______________|___________|________________  
               |           |

What I am trying to do is this: Select data from the routepoints table for a given list of user_id's where routepoint_time has the largest value.

I am trying to achieve this through PHP and Mysqli where I give the following query an array of user_id:

$stmt = $this->con->prepare("SELECT routes.user_id, routepoints.latitude, routepoints.longitude, MAX(routepoints.routepoint_time) AS maxTime, users.status
                            FROM routepoints
                            INNER JOIN routes ON routepoints.route_id = routes.route_id
                            INNER JOIN users ON routes.user_id = users.user_id
                            WHERE routes.user_id IN ({$idArray})
                            GROUP BY routes.user_id");
            $stmt->execute();
            $stmt->store_result();
            $stmt->bind_result($user_id, $latitude, $longitude, $routePointTime, $status);

The above query returns the following results:

{
  "status": true,
  "friendsLocationList": [
    {
      "userId": 16,
      "latitude": 12.83276562,
      "longitude": 12.83276562,
      "routePointTime": "64321325541",
      "onlineStatus": 0
    },
    {
      "userId": 18,
      "latitude": 9.32454363,
      "longitude": 10.65785453,
      "routePointTime": "3324546454363",
      "onlineStatus": 0
    }
  ]
}

As you can see, the problem is that it returns the largest value of routepoint_time, but it doesn't match the rest of the associated values (latitude, longitude etc).

How can I achieve this?

Answer 1

Edit: Please only use the solution in my answer, if you're not interested in best performance possible. Otherwise take a look into the mysql-manual (link posted by strawberry in comments to the question).

SELECT
    R.user_id,
    RP.route_id,
    RP.latitude,
    RP.longitude,
    RP.routepoint_time AS maxTime

FROM routepoints AS RP

    INNER JOIN routes AS R
    ON RP.route_id = R.route_id

    INNER JOIN users
    ON R.user_id = users.user_id

WHERE
    RP.routepoint_time = (
        SELECT max(RP2.routepoint_time)
        FROM routepoints AS RP2
        WHERE RP2.route_id = RP.route_id
    )
    R.user_id IN (16,18)

Loops through every record from the routepoints-table. The subselect of the where-condition finds the longest routepoint belonging to the current Route from the first loop and ensures only the routepoint with that time is selected.

I'm not sure this will work in every case, as it seems to me that the where condition will not work in some edge-case.