MySQL select rows with MAX Retrieving the last record in each group and other field

Question

Here is a table vehicle_connection_status that contains data as shown below:

+------------+-------------+---------------+-------------+--------------+
| "idStatus" | "vehicleId" | "isConnected" | "missionId" | "lastUpdate" |
+------------+-------------+---------------+-------------+--------------+
| "8"        | "1"         | "0"           | "1"         | "10"         |
| "9"        | "1"         | "1"           | "1"         | "9"          |
| "10"       | "2"         | "0"           | "1"         | "9"          |
| "11"       | "2"         | "1"           | "1"         | "8"          |
| "12"       | "3"         | "1"           | "1"         | "11"         |
| "13"       | "4"         | "0"           | "1"         | "9"          |
| "14"       | "4"         | "1"           | "1"         | "10"         |
| "15"       | "4"         | "0"           | "1"         | "11"         |
+------------+-------------+---------------+-------------+--------------+

If I run the query

select vehicleId, isConnected, max(lastUpdate) lasts    
from vehicle_connection_status 
where missionId =1 
group by vehicleId, isConnected

I will get the result as:

+-------------+---------------+---------+
| "vehicleId" | "isConnected" | "lasts" |
+-------------+---------------+---------+
| "1"         | "0"           | "10"    |
| "1"         | "1"           | "9"     |
| "2"         | "0"           | "9"     |
| "2"         | "1"           | "8"     |
| "3"         | "1"           | "11"    |
| "4"         | "0"           | "11"    |
| "4"         | "1"           | "10"    |
+-------------+---------------+---------+

What i want is the file with higest "lasts"per "vehicleId" but with "isConnected" and "vehicleId" fields, the result I'm looking for is:

+-------------+---------------+---------+
| "vehicleId" | "isConnected" | "lasts" |
+-------------+---------------+---------+
| "1"         | "0"           | "10"    |
| "2"         | "0"           | "9"     |
| "3"         | "1"           | "11"    |
| "4"         | "0"           | "11"    |
+-------------+---------------+---------+

Basically add "isconected" to

select vehicleId, max(lastUpdate) lasts  
    from vehicle_connection_status where missionId =1 group by vehicleId

+-------------+---------+
| "vehicleId" | "lasts" |
+-------------+---------+
| "1"         | "10"    |
| "2"         | "9"     |
| "3"         | "11"    |
| "4"         | "11"    |
+-------------+---------+

I have seen other similar questions:

Retrieving the last record in each group - MySQL

but I'm not able to resolve the problem.

The problem is that I don't know how to avoid grouping by isconnected when I need it as a result.

I'm getting the following error:

/* Error de SQL (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'x.idStatus' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by */

I have tried with other approachs:

distinct:

SELECT DISTINCT
  vehicleId,
  isconnected,
  lastUpdate
FROM vehicle_connection_status t1
WHERE lastUpdate in (SELECT
  MAX(t2.lastUpdate)
FROM vehicle_connection_status t2
GROUP BY vehicleId)

And other more esoteric,

select v1.vehicleId, v1.isConnected , v1.lastUpdate
    from vehicle_connection_status v1

inner join 
    (select v2.vehicleId, v2.isConnected , max(v2.lastUpdate) as latest
    from vehicle_connection_status v2 group by v2.vehicleId, v2.isConnected) vc

    on vc.vehicleId = v1.vehicleId and vc.latest = v1.lastUpdate


where v1.missionId =1

But doesn't work.

At present, I use the first query an get the desired results parsing the result set programaticaly, but It´s not the best solution.

To test:

DB-FIDDLE

Please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, May 20 '20 at 12:31

score 1 · Accepted Answer · answered May 20 '20 at 12:44

Can you try this

SELECT 
    v1.vehicleId, v1.isConnected, v1.lastUpdate
FROM
    vehicle_connection_status v1
        JOIN
    (SELECT 
        vehicleId, MAX(lastUpdate) lastUpdate
    FROM
        vehicle_connection_status 
    GROUP BY vehicleId) v2 ON v1.vehicleId = v2.vehicleId
        AND v1.lastUpdate = v2.lastUpdate;

MySQL select rows with MAX Retrieving the last record in each group and other field

1 Answers1