order by with a case expression

Question

I have a complex order by with case expression.

ORDER BY CASE WHEN field_1 = ? then 0 
              WHEN field_2 = ? then 1 ELSE 3 end ASC

i want to order the inner results by a field_3

for example my table is -

index | field_1 | field_2 | field_3
  0   |   car   |  red    |   2
  1   |   hat   |  red    |   5
  2   |   red   |  house  |   4

and ? = red

So index 0 and 1 will get 1 and index 2 will get 0 from the case expression.

and i want to order again by field_3 but the order should only be between the values that are the same.

so index 0,1 should order by field_3

Any suggestions how to achieve this?

---

The result should be -

index 2 -> index 0 -> index 1

Answer 1

Not sure I understand but why not just do

ORDER BY CASE WHEN field_1 = ? then 0 
              WHEN field_2 = ? then 1 ELSE 3 end ASC, field_3 ASC

If the first part (the CASE) are equal for two or more rows then those rows will be sorted by field_3