John Lang John Lang - 2 months ago 7
MySQL Question

MySQL ORDER BY CASE not working

I am trying to sort out my results in order of matching section, subdivision, and key map. Right Now I get error however I think I am on the right track to accomplishing this. I cannot figure out how to properly setup my case to do it in this order.

EDIT:

I've updated the query which works, but its still not sorting in the correct order.

SELECT
list.WorkOrder,
list.Address,
locateparsed.CrossStreet,
list.Section,
list.Subdivision,
locateparsed.KeyMap,
locateparsed.City,
list.Score
FROM list
LEFT JOIN locateparsed ON locateparsed.WorkOrder = list.WorkOrder
WHERE locateparsed.City = "PEARLAND"
AND list.Completed = 0
AND list.Reference = 0
AND locateparsed.Ticket IS NOT NULL
GROUP BY list.WorkOrder
ORDER BY CASE
WHEN list.Section = 'Sec. 1' THEN 1
WHEN list.Subdivision = 'Shadow Ridge' THEN 2
WHEN locateparsed.KeyMap = '612P' THEN 3
END


Output Example:

WO | Address | CrossStreet | Section | Subdivision | KeyMap | City
1 | 123 test | other | Sec. 1 | Shadow Ridge | 612P | Pearland
2 | 111 test | other | Sec. 1 | Shadow Ridge | 612P | Pearland
3 | 333 other | bleach | Sec. 2 | Shadow Ridge | 612P | Pearland
4 | 334 other | bleach | Sec. 2 | Shadow Ridge | 612P | Pearland
5 | 848 other | bleach | Sec. 2 | Shadow Ridge | 612P | Pearland
6 | 733 blah | random | Sec. 5 | Bright Ridge | 612P | Pearland
7 | 722 hmm | blah | Sec. 5 | Bright Ridge | 612P | Pearland
8 | 723 hmm | blah | Sec. 5 | Bright Ridge | 612P | Pearland
9 | 111 asdf | fdas | Sec. 30 | Other Ridge | 650P | Pearland
10 | 123 asdf | fdas | Sec. 30 | Other Ridge | 650P | Pearland

Answer

Your syntax is wrong. It should be:

ORDER BY CASE
    WHEN list.Section = 'Sec. 1' THEN 1
    WHEN list.Subdivision = 'Shadow Ridge' THEN 2
    WHEN locateparsed.KeyMap = '612P' THEN 3
    ELSE 4 -- anything that doesn't match above conditions comes last
END

There's just one END for the whole CASE expression, not after each WHEN, and no comma between the WHEN clauses.