I've already looked at these two questions:
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:
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.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With