CODEWITHSUNDEEP
mysqlsql
user3243180
user3243180

Reputation: 57

sorting multiple column mysql

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

Upvotes: 3

Views: 79

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Try this order by. Use case statement to custom the sort order

SELECT *
FROM   yourtable
ORDER  BY CASE
            WHEN value = 0 THEN rating
            ELSE key
          END DESC

Upvotes: 1

default locale
default locale

Reputation: 13426

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.

Upvotes: 2

Related Questions