MySQL and MariaDB Compatibility - SQL query to comply with 'ONLY_FULL_GROUP_BY

[dvdcut]

this issue is driving from old rejected pull request: #11

MySQL versions >5.7.5 now have a default 'ONLY_FULL_GROUP_BY' SQL mode, so default MySQL installation will throw a wobbly: "Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns." AFAICS, 'time_id' is unique and there is only one 'temperature' set for each row, so I can't see the reason for the "MAX(temperature) as max_c" (which necessitates the 'GROUP BY') ... am I missing something? I also removed the 'seconds' from the schedules displayed as they take up valuable screen space & tell us nothing.

when i try to execute following query and google first link is to this pull request #11.

https://user-images.githubusercontent.com/53821067/88051077-f4e93300-cb4f-11ea-9c7e-5d244b6683ae.png

SELECT time_id, time_status, `start`, `end`, WeekDays,tz_id, tz_status, zone_id, index_id, zone_name, `category`, temperature, FORMAT(max(temperature),2) as max_c, sch_name 
FROM schedule_daily_time_zone_view WHERE holidays_id = 0 AND tz_status = 1 group by time_id ORDER BY start, sch_name asc;

@pihome-shc gave fix for this one query

SELECT any_value(sdtzv.time_id) as time_id, any_value(sdtzv.time_status) as time_status, any_value(sdtzv.start) as start, any_value(sdtzv.end) as end, any_value(sdtzv.WeekDays) as WeekDays, any_value(sdtzv.tz_id) as tz_id,
any_value(sdtzv.tz_status) as tz_status, any_value(sdtzv.zone_id) as zone_id, any_value(sdtzv.index_id) as index_id, any_value(sdtzv.zone_name) as zone_name, any_value(sdtzv.category) as category, any_value(sdtzv.temperature) as temperature,
FORMAT(max(any_value(sdtzv.temperature)),2) as max_c, any_value(sdtzv.sch_name) as sch_name
FROM schedule_daily_time_zone_view sdtzv
WHERE holidays_id = 0 AND tz_status = 1 group by time_id ORDER BY start, sch_name asc;

mariadb do not have the 'ANY VALUE' function. link

after have gone through all homelist, schedules, there aren't many query that need changing

[pihome-shc]

i had lots of issues in start with this sql but for me workaround was to turn off the ONLY_FULL_GROUP_BY MySQL Mode, this was the very reason behind raspberry pi img file as i had to many emails from users with all sorts of issues and linux is ever changing environment and what instructions are correct today may not be valid tomorrow and its very hard to keep up with all that.

i know any_value function isnt available in MariaDB (which PiHome raspberry pi img is based on) that any_value is not really a workaround so you can use it in both version and on top of this it isnt recommended either here is link to why but i m using it on PiConnect site until i find better solution. #11 was rejected as we were and we are using group by in schedules and may be many other places.