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

What should happen if there's another record with value=0 and rating=2? — default locale
– default locale, Commented Jan 28, 2015 at 7:12
@default locale : actually the third column is a date time field in my real table. so the values in the third column will be unique. i have changed the column names for this question — user3243180
– user3243180, Commented Jan 28, 2015 at 7:15
well, you can substitute numbers back to corresponding dates :) My question is: if you have non-special value with rating equal to special value which record should go first? — default locale
– default locale, Commented Jan 28, 2015 at 7:17

default locale · Accepted Answer · 2015-01-28 07:34:15Z

2

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.

edited Jan 28, 2015 at 7:34

answered Jan 28, 2015 at 7:28

default locale

13.6k13 gold badges60 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3243180 Over a year ago

when i run this query, i am getting {0,0,3}, {0,1,2}, {1,1,1}. So its ordered by rating . but its not ordered by key in descending order

default locale Over a year ago

Please check out the updated question. First time, I forgot key desc, but now it should work.

Pரதீப் · Accepted Answer · 2015-01-28 07:57:55Z

1

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

edited Jan 28, 2015 at 7:57

answered Jan 28, 2015 at 7:10

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

sorting multiple column mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related