sorting multiple column mysql

Question

i have a table like this :

key   value   rating

1      1       1

0      0       3

0      1       2

First i want to order the table by rating column in descending order and for those rows where value column is 1 , it should be ordered by key in descending order. So the resulting table has to be like this:

key   value   rating

0      0       3

1      1       1

0      1       2

i have tried this :

SELECT * FROM `table` ORDER BY CASE `value` WHEN 1 THEN `key` 
        END 
 `table`.`pair` desc

Answer 1

You didn't specify how records with value=1 and value<>1 should be ordered in respect to each other. So, I'll guess that value=1 records will come last:

SELECT *
FROM table
ORDER BY
    (
    CASE
        WHEN value=1 THEN -1
        ELSE rating
    END
    ) desc,
    key desc

If you have negative ratings you can replace -1 with your negative limit. The goal is to sort value=1 records separately.

Check out this fiddle. I added some records to illustrate issues in the problem statement.

Currently it's unclear from your question how records like (0,1,4), (1,1,2) and (0,0,3) should be ordered.