edwardmp edwardmp - 6 months ago 45
SQL Question

Rewrite query to prevent only_full_group_by error

I have the following query:

SELECT f.flight_number, f.flight_date, f.airport, a.iata, a.iso3166 AS airport_country_code, air.icao
FROM flights_database f
LEFT JOIN airlines air ON air.name = f.airline
JOIN airports a ON f.airport = a.airportNameClean
WHERE f.flight_date
BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_ADD(CURDATE() , INTERVAL 1 DAY)
GROUP by f.flight_date, f.airport, f.flight_number, a.iata
ORDER by f.airport, f.flight_number


Now I have upgraded MysQL and the
only_full_group_by setting
is activated.
This query no longer works in this form:

Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'air.ICAO' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


Of course I could disable this setting, but I do understand this setting was introduced for a reason. Simply adding
icao
to the
GROUP BY
clause introduces different results.

How can I rewrite this query to achieve the same behavior with this setting enabled?

Answer

Can't you simply do max(icao)?

SELECT f.flight_number, f.flight_date, f.airport, a.iata, a.iso3166 AS airport_country_code, max(air.icao)
        FROM flights_database f
        LEFT JOIN airlines air ON air.name = f.airline
        JOIN airports a ON f.airport = a.airportNameClean
        WHERE f.flight_date
        BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND DATE_ADD(CURDATE() , INTERVAL 1 DAY)
        GROUP by f.flight_date, f.airport, f.flight_number, a.iata
        ORDER by f.airport, f.flight_number
Comments