Grupo MySQL por order / prioridad de otra columna

Ya he visto estas dos preguntas:

  • Agrupación por columna con dependencia de otra columna
  • MySQL GROUP BY con preference

Sin embargo, ambos usan una function agregada MAX para get el valor más alto o más completo, lo que no funciona para mi caso.

A los fines de esta pregunta, simplifiqué mi situación. Aquí está mi información actual: Muestra de base

Me gustaría get el nombre del operador para cada ruta, pero con respecto a la dirección de viaje (es decir, orderar o "preferir" los valores). Este es mi pseudo-código:

if(`direction` = 'west' AND `operatorName` != '') then select `operatorName` else if(`direction` = 'north' AND `operatorName` != '') then select `operatorName` else if(`direction` = 'south' AND `operatorName` != '') then select `operatorName` else if(`direction` = 'east' AND `operatorName` != '') then select `operatorName` 

Mi consulta SQL actual es:

 SELECT route, operatorName FROM test GROUP BY route 

Esto me da la agrupación, pero el operador equivocado para mis propósitos:

 route | operatorName -------------------- 95 | James 96 | Mark 97 | Justin 

He intentado aplicar una cláusula ORDER BY pero GROUP BY tiene prioridad. Cuál es mi resultado deseado:

 route | operatorName -------------------- 95 | Richard 96 | Andrew 97 | Justin 

No puedo hacer MAX() aquí ya que "norte" viene antes que "sur" en order alfabético. ¿Cómo declaro explícitamente mi preference / order antes de aplicar la cláusula GROUP BY ?

También tenga en count que las cadenas vacías no son preferidas.

Tenga en count que este es un ejemplo simplificado. La consulta real selecciona muchos más campos y se une con otras tres tablas, pero no hay funciones agregadas en la consulta.

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