test, MySQL - rextester

Language: Layout:

#mysql 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating

DROP TABLE IF EXISTS `Facility`;
DROP TABLE IF EXISTS `Facility2`;

CREATE TABLE Facility
    (`FacilityID` int, `ChemicalID` int, `ZoneType` varchar(1), `ZoneDistance` int)
;
    
INSERT INTO Facility
    (`FacilityID`, `ChemicalID`, `ZoneType`, `ZoneDistance`)
VALUES
    (1, 2, 'A', 1000),
    (1, 2, 'B', 900),
    (1, 2, 'C', 500),
    (1, 5, 'A', 1200),
    (1, 5, 'B', 900),
    (1, 7, 'B', 2000),
    (1, 7, 'C', 900),
    (2, 13, 'A', 200),
    (2, 13, 'B', 300),
    (2, 13, 'C', 600)
;

SELECT *
FROM (
        SELECT 
            t1.`FacilityID`,
            t1.`ChemicalID`, 
            t1.`ZoneType`, 
            t1.`ZoneDistance`,
             ( SELECT COUNT(*)
               FROM Facility as t2
               WHERE t1.`FacilityID` = t2.`FacilityID`
                 AND ( ( t1.`ZoneDistance` < t2.`ZoneDistance`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` > t2.`ZoneType`)
                    OR ( t1.`ZoneDistance` = t2.`ZoneDistance` and t1.`ZoneType` = t2.`ZoneType` and t1.`ChemicalID` > t2.`ChemicalID`)
                     )
             ) as rn
        FROM       Facility AS t1
        ORDER BY 
            t1.`FacilityID`,        
            t1.`ZoneDistance` DESC,
            t1.`ZoneType`, 
            t1.`ChemicalID`
    ) T
WHERE rn = 0;


absolute service time: 0,41 sec