MYSQL prioritise which record is selected by GROUP BY

Question

I have written a MYSQL script and am having trouble to get the results I need. Its hard to explain what I am trying to do so here is an example.

This is my MYSQL script:

SELECT device_uuid,
       gender,
       day_epoch,
       hour,
       age
FROM daily_stats_venue_unique_device_uuids_per_hour
WHERE (day_epoch + (3600*hour)) >= 1519171200 
 AND (day_epoch + (3600*hour)) < 1519340400 
 AND venue_id = 4
GROUP BY device_uuid

and this is returning these results:

(There are 2 device_uuid in there which looks very similar but one has a 6 instead of a 5 near the start. Sorry about that it makes my next screenshot more confusing but please look closely.)

but when I modify the group by to this: GROUP BY device_uuid, gender I get these results:

As you can see there are 2 records with the same device_uuid (records 2/3) but they have different genders. I want to use the first query I showed you but if a record has a gender I would like to get that result instead of the one without a gender.

Thank you for any help in advance

try `SELECT * FROM (SELECT device_uuid, gender, day_epoch, hour, age FROM daily_stats_venue_unique_device_uuids_per_hour WHERE (day_epoch + (3600*hour)) >= 1519171200 AND (day_epoch + (3600*hour)) < 1519340400 AND venue_id = 4 ORDER BY gender DESC ) t GROUP BY t.device_uuid` — Tamil Selvan C, Feb 23 '18 at 16:32
@TamilSelvanC thanks for the reply, that is still doing the same as the first set of results I showed, it isn't prioritising if it has a gender :( — Luke Rayner, Feb 23 '18 at 16:35
please, be very careful when using group by. it is intended to be used in conjunction with aggregation functions. in your case, your risk is to have inconsistent results, because, once grouped by uuid, you don't know where the gender, the day_epoch etc will be taken from. this query is not really meaningful, and you probably should use some joins — senape, Feb 23 '18 at 16:51

score 0 · Answer 1 · answered Feb 23 '18 at 16:44

Taken from this answer:

SELECT * FROM (
    SELECT device_uuid,
           gender,
           day_epoch,
           hour,
           age
    FROM daily_stats_venue_unique_device_uuids_per_hour
    WHERE (day_epoch + (3600*hour)) >= 1519171200 
     AND (day_epoch + (3600*hour)) < 1519340400 
     AND venue_id = 4
    ORDER BY gender DESC
) t
GROUP BY device_uuid

Take note of the warning on that answer:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

Further notes, this answer may also give you some direction as well to another possible solution.

MYSQL prioritise which record is selected by GROUP BY

1 Answers1