Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL group by with ordering/priority of another column

I've already looked at these two questions:

  • Grouping by Column with Dependence on another Column
  • MySQL GROUP BY with preference

However both of them use an aggregate function MAX in order to obtain the highest or filled in value, which doesn't work for my case.

For the purposes of this question, I've simplified my situation. Here is my current data:

Database sample

I'd like to obtain the operator name for each route, but with respect to direction of travel (i.e. ordering or "preferring" values). This is my pseudo-code:

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`

My current SQL query is:

SELECT route, operatorName
FROM test
GROUP BY route

This gives me the grouping, but wrong operator for my purposes:

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

I have tried applying a ORDER BY clause but GROUP BY takes precedence. What my desired result is:

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

I cannot do MAX() here as "north" comes before "south" in alphabetical order. How do I explicitly state my preference/ordering before the GROUP BY clause is applied?

Also keep in mind that empty strings are not preferred.

Please note that this is a simplified example. The actual query selects a lot more fields and joins with three other tables, but there are no aggregate functions in the query.

like image 692
rink.attendant.6 Avatar asked Oct 03 '22 10:10

rink.attendant.6


2 Answers

You can use that MAX example, you just needed to "fake it". See here: 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
like image 60
AgRizzo Avatar answered Oct 07 '22 18:10

AgRizzo


I came up with this solution, however, it is ugly. Anyway, you may try it:

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
;

Firstly, we select all records with direction changed to a number so it is in required order. Then, we GROUP by each route and get the minimal direction. What is left remains in the outermost query - select the operator name based on the lowest found direction.

Output:

ROUTE  OPERATOR_NAME
95  Richard
96  Andrew
97  Justin

Please, next time attach sample data not as a picture, but either as a plain text, or as inserts (best at SQLFiddle).

Check this solution at SQLFiddle

like image 45
Przemyslaw Kruglej Avatar answered Oct 07 '22 19:10

Przemyslaw Kruglej