John Lang John Lang - 1 year ago 57
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download