CODEWITHSUNDEEP
mysql
Amy Neville
Amy Neville

Reputation: 10601

Conditional MySQL Order By

I have a problem with conditional ordering of a mysql query

  1. I want to give multiplier first priority for descending order

  2. At the second level of priority I want to order by pot in descending order (but only where pot > 80)

  3. At the third level of priority I want to order by points

My problem is how to order by pot but only if pot is > a certain amount.

SELECT NAME, POINTS, POT, MULTIPLIER
FROM ITEMS 
ORDER BY MULTIPLIER DESC, 
(CASE WHEN (POT > 80) THEN 'POT DESC,POINTS DESC'
ELSE 'POINTS DESC' END)

This is what I tried but, if it meets the condition then it stops ordering by points on the third level of priority...I want it to always order by points on third level priority

example order I would like:

multiplier(4),pot(20),points(144)
multiplier(2),pot(90),points(130)
multiplier(2),pot(90),points(13)
multiplier(1),pot(70),points(16783)
multiplier(1),pot(77),points(1653)
multiplier(1),pot(78),points(163)

Upvotes: 2

Views: 143

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43444

I think this is what you're looking for:

SELECT NAME, POINTS, POT, MULTIPLIER
FROM ITEMS
ORDER BY
  multiplier DESC,
  pot <= 80,
  points DESC

Upvotes: 2

pobrelkey
pobrelkey

Reputation: 5973

ORDER BY multiplier DESC, (CASE WHEN pot > 80 THEN pot ELSE 80 END) DESC, points DESC

Upvotes: 0

Related Questions