John Lang John Lang - 2 months ago 6
MySQL Question

Sort within case based on row result desc

Right now when I run my query, it does not order the final Score column from highest to lowest. I cannto seem to figure out how I can put that within my case either. I need at least the first and second case to sort that final column.

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.Address LIKE '%VINTAGE PARK LN' THEN 1
WHEN locateparsed.Address LIKE '%SHADOW RIDGE LN' THEN 2
WHEN list.Section = 'Sec. 1' AND list.Subdivision = 'Shadow Ridge' THEN 3
WHEN list.Subdivision = 'Shadow Ridge' THEN 4
WHEN locateparsed.KeyMap = '612P' THEN 5
WHEN locateparsed.KeyMap = '612' THEN 6
ELSE 7
END


Output Example I need:

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

Answer

I think you just need to include score as a second sort key:

ORDER BY (CASE WHEN list.Address LIKE '%VINTAGE PARK LN' THEN 1
               WHEN locateparsed.Address LIKE '%SHADOW RIDGE LN' THEN 2
               WHEN list.Section = 'Sec. 1' AND list.Subdivision = 'Shadow Ridge' THEN 3
               WHEN list.Subdivision = 'Shadow Ridge' THEN 4
               WHEN locateparsed.KeyMap = '612P' THEN 5
               ELSE 7
          END),
         score DESC

This sorts all of groups by score as the second key. If you specifically only need this for the first two groups, then you would need to include a second CASE for the score sort.

Comments