CODEWITHSUNDEEP
mysqlsql
jim
jim

Reputation: 1

How can I order multiple columns in mysql?

I have this table in my mysql:

| id | category_id | region_id | score | +----+-------------+-----------+-------+ | 1 | 1 | 1 | 78 | | 2 | 1 | 2 | 65 | | 3 | 1 | 3 | 98 | | 4 | 1 | 4 | 45 | | 5 | 1 | 5 | 78 | | 6 | 1 | 1 | 98 | | 7 | 1 | 2 | 32 | | 8 | 1 | 3 | 56 | | 9 | 1 | 4 | 89 | | 10 | 1 | 5 | 65 | +----+-------------+-----------+-------+

I want to get the 5 latest id but order my result table base on region id so I used this code

SELECT * 
  FROM tb_scores 
 WHERE category_id = 1 
 ORDER 
    BY id DESC
     , region_id ASC
 LIMIT 5

but the result only sorted the id as desc but not the region_id as ASC..to explain briefly I want this kind of result.

| id | category_id | region_id | score |
+----+-------------+-----------+-------+
|  6 |           1 |         1 |    98 |
|  7 |           1 |         2 |    32 |
|  8 |           1 |         3 |    56 |
|  9 |           1 |         4 |    89 |
| 10 |           1 |         5 |    65 |
+----+-------------+-----------+-------+

Upvotes: 0

Views: 64

Answers (1)

Deep
Deep

Reputation: 3202

try this:

SELECT *
FROM   (SELECT *
        FROM   tb_scores
        WHERE  category_id = 1
        ORDER  BY id DESC,region_id ASC LIMIT 5) t
ORDER  BY region_id

get your data in subquery and apply order by region_id on the subquery result.

Upvotes: 3

Related Questions