CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
John James
John James

Reputation: 657

Sort by multiple columns MySQL

I have a table in which I have three fields with data type INT, INT and INT.

I want to sort my select query using all these three columns. Sort by ASC if field A <= 10, sort by DESC field2 and sort by ASC field 3.

SELECT * FROM table1 WHERE id=uid     
ORDER BY 
    CASE table1.field1
        WHEN table1.field1 < 11 THEN table1.field1
        END
    ASC, 
table1.field2 DESC,
table1.field3 ASC;

+------+--------+---------+
|field1| field2 | field3  |
+------+--------+---------+
|   1  |    4   |    1    |
+------+--------+---------+
|   2  |    3   |    2    |
+------+--------+---------+
|   9  |    2   |    4    |
+------+--------+---------+
|  10  |    1   |    7    |
+------+--------+---------+

For some reason the CASE doesnt really work, if I exclude that it works but does sort all of field1 in ASC order while I only want the 10 first.

Upvotes: 1

Views: 58

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

There is no need for table1.field1 before WHEN:

SELECT * 
FROM table1 
WHERE id = uid     
ORDER BY CASE WHEN table1.field1 < 11 THEN table1.field1 END ASC, 
  table1.field2 DESC,
  table1.field3 ASC;

Explanation:

CASE table1.field1
     WHEN table1.field1 < 11 THEN table1.field1
END       -- this evaluates to true/false (1 and 0)

=>
CASE table1.field1 WHEN 1 THEN table1.field1 END

Upvotes: 2

Related Questions