MySQL - MIN and MAX value with empty fields

Question

I'm trying to write an SQL that will allow me to return the minimum value for a start time and a maximum time for an end time. My problem is that in the table I can have empty rows and when I do a MIN on an empty field it returns me an empty value. I can't do begin_service! = '' because I may not have values, in this case I must have an empty result. This is my table :

app_id	function_id	begin_service	end_service
B125	12
B125	13
B125	54
C789	98
C789	12	06:00	18:00
C789	15	08:00	20:00
C789	78

My SQL :

SELECT app_id, MIN(begin_service), MAX(end_service)
FROM applications
GROUP BY app_id;

Result :

app_id	begin_service	begin_service
B125
C789		20:00

Desired result :

app_id	begin_service	begin_service
B125
C789	06:00	20:00

Can you help me ?

Answer 1

Use two subqueries to get the empty and non-empty minimum values for each app_id. Combine them with UNION and then take the max of that to prefer the non-empty value.

SELECT app_id, MAX(begin_service) AS begin_service, MAX(end_service) AS end_service
FROM (
    SELECT app_id, MIN(begin_service) AS begin_service, MAX(end_service) AS end_service
    FROM applications
    WHERE begin_service != ''
    GROUP BY app_id

    UNION ALL

    SELECT app_id, '', MAX(end_service)
    FROM applications
    WHERE begin_service = ''
    GROUP BY app_id
) AS x
GROUP BY app_id