CODEWITHSUNDEEP
mysql
John Lang
John Lang

Reputation: 85

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

Upvotes: 0

Views: 1000

Answers (1)

Barmar
Barmar

Reputation: 780843

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.

Upvotes: 1

Related Questions