Grupo MySQL por order / prioridad de otra columna

Puedes usar ese ejemplo MAX, solo necesitas "fingirlo". Vea aquí: http://sqlfiddle.com/#!2/58688/5

 SELECT * FROM test JOIN (SELECT 'west' AS direction, 4 AS weight UNION SELECT 'north',3 UNION SELECT 'south',2 UNION SELECT 'east',1) AS priority ON priority.direction = test.direction JOIN ( SELECT route, MAX(weight) AS weight FROM test JOIN (SELECT 'west' AS direction, 4 AS weight UNION SELECT 'north',3 UNION SELECT 'south',2 UNION SELECT 'east',1) AS priority ON priority.direction = test.direction GROUP BY route ) AS t1 ON t1.route = test.route AND t1.weight = priority.weight 

Se me ocurrió esta solución, sin embargo, es fea. De todos modos, puedes intentarlo:

 CREATE TABLE test ( route INT, direction VARCHAR(20), operatorName VARCHAR(20) ); INSERT INTO test VALUES(95, 'east', 'James'); INSERT INTO test VALUES(95, 'west', 'Richard'); INSERT INTO test VALUES(95, 'north', 'Dave'); INSERT INTO test VALUES(95, 'south', 'Devon'); INSERT INTO test VALUES(96, 'east', 'Mark'); INSERT INTO test VALUES(96, 'west', 'Andrew'); INSERT INTO test VALUES(96, 'south', 'Alex'); INSERT INTO test VALUES(96, 'north', 'Ryan'); INSERT INTO test VALUES(97, 'north', 'Justin'); INSERT INTO test VALUES(97, 'south', 'Tyler'); SELECT route, (SELECT operatorName FROM test WHERE route = t2.route AND direction = CASE WHEN direction_priority = 1 THEN 'west' WHEN direction_priority = 2 THEN 'north' WHEN direction_priority = 3 THEN 'south' WHEN direction_priority = 4 THEN 'east' END) AS operator_name FROM ( SELECT route, MIN(direction_priority) AS direction_priority FROM ( SELECT route, operatorName, CASE WHEN direction = 'west' THEN 1 WHEN direction = 'north' THEN 2 WHEN direction = 'south' THEN 3 WHEN direction = 'east' THEN 4 END AS direction_priority FROM test ) t GROUP BY route ) t2 ; 

En primer lugar, seleccionamos todos los loggings con la direction cambiada a un número para que esté en el order requerido. Luego, GROUP por cada ruta y obtenemos la dirección mínima. Lo que queda permanece en la consulta más externa: select el nombre del operador según la dirección más baja encontrada.

Salida:

  RUTA OPERATOR_NAME
 95 Richard
 96 Andrew
 97 Justin 

Por favor, la próxima vez adjunte los datos de muestra no como una image, sino como text plano o como inserciones (mejor en SQLFiddle ).

Verifique esta solución en SQLFiddle

Puedes enumerar las instrucciones usando una construcción de caso para hacer que se puedan orderar en tu order. luego clasifique las direcciones divididas por ruta y luego solo select el primer candidato.

 set @c = 1; set @r = ''; select route , direction , operatorName from ( select route , direction , operatorName , @c := if (@r = route, @c + 1, 1) as cand from ( select route , case when direction = 'west' then 1 when direction = 'north' then 2 when direction = 'south' then 3 when direction = 'east' then 4 else 5 end as enum_direction , direction , operatorName ) order by route , enum_direction ) 

 select * from routes r1 where exists ( select 1 from routes r2 where r1.route_id = r2.route_id group by r2.route_id having min(case r1.direction when 'west' then 1 when 'north' then 2 when 'south' then 3 when 'east' then 4 end) = min(case r2.direction when 'west' then 1 when 'north' then 2 when 'south' then 3 when 'east' then 4 end) ) 

manifestación