Mysql order group by weird behaviour

Question

I am trying to select first row of each group in mysql. I followed examples like How to select the first row for each group in MySQL?

For some reason this does not work for me. Having the following sql

SELECT stationID, vrID, vrsCreatedAt FROM (
SELECT VRS.stationID, VR.vrVehicleID, VRS.vrID, VRS.vrsCreatedAt FROM VehicleRoutes VR
    LEFT OUTER JOIN VehicleRouteStations VRS ON VRS.vrID = VR.vrID
    WHERE VR.vrRouteID = 8 AND VR.vrStatus = 'active' AND VR.vrID = 65
    ORDER BY VRS.vrsCreatedAt DESC) x

And the result set is

43  65  2017-02-06 17:15:14
9   65  2017-02-06 17:13:12
42  65  2017-02-06 17:09:25
41  65  2017-02-06 17:07:14
69  65  2017-02-06 17:03:58
........
42  65  2017-01-17 16:35:47
63  65  2017-01-17 14:34:57
322 65  2017-01-17 14:31:45
315 65  2017-01-17 13:53:33

When i apply the group by statemenet

SELECT stationID, vrID, vrsCreatedAt FROM (
SELECT VRS.stationID, VR.vrVehicleID, VRS.vrID, VRS.vrsCreatedAt FROM VehicleRoutes VR
    LEFT OUTER JOIN VehicleRouteStations VRS ON VRS.vrID = VR.vrID
    WHERE VR.vrRouteID = 8 AND VR.vrStatus = 'active' AND VR.vrID = 65
    ORDER BY VRS.vrsCreatedAt DESC) x GROUP BY vrID

I get

315 65  2017-01-17 13:53:33

This is definetely not first row, its the last one. Even if i try ORDER BY VRS.vrsCreatedAt DESC i get the same behaviour.

I know this should work since i've used before. Maybe in the latest MySQL version 5.7.17-0ubuntu0.16.04.1 (Ubuntu) this does not work anymore?

Thanks

what group? please provide sample data and expected output along with explanation of the logic. — Gurwinder Singh, Feb 06 '17 at 17:21
`GROUP BY` does not guarantee specific results for non-aggregated, non-grouped fields. — Uueerdo, Feb 06 '17 at 17:26

score 0 · Accepted Answer · answered Feb 06 '17 at 17:42

You have to specifically say what you want in the result. There are multiple rows "aggregated" into one row.

For the field you group on, you get the same value obviously, so this is not an issue. All the other fields must be hinted at. You don't get "the same field' or something like that, you get either

the field specified by your aggregation (e.g. "max", or "min" or "sum")
a unspecified field (not to say random, there is a 'rule' in there, but it can be changed and is implementation-dependent.

bottomline is: if you want to get a specific result, you should either not do this with a group-by (select the results in a subquery?) or just use an aggregation for that field.

Mysql order group by weird behaviour

1 Answers1