CODEWITHSUNDEEP
mysqlsql
Peter
Peter

Reputation: 1541

Mysql customise order by with group by

When I 

SELECT * FROM table_A WHERE id = 2321 order by color asc, color = 49, color = 2;

this will return all which color = 2, then color = 49 then all others color.

But I need only the group by result which I then

SELECT * FROM table_A WHERE id = 2321 GROUP BY color ORDER BY color asc, color = 49, color = 2;

it will return group by result but have disregard the order

My question is how to I return color which start from 2, then 49, then other and group the 'color'?

Upvotes: 1

Views: 57

Answers (2)

peterm
peterm

Reputation: 92785

If you care for the order of the rest of the colors you can use CASE

SELECT color 
  FROM table_A 
 WHERE id = 2321 
 GROUP BY color
 ORDER BY CASE color 
            WHEN 2 THEN 1 
            WHEN 49 THEN 2
            ELSE color + 50
          END

This will sort 2 first, 49 second, and then other colors ordered ASC. That's assuming that color is always positive int.

SQLFiddle

Upvotes: 0

John Woo
John Woo

Reputation: 263713

GROUP BY is not needed here. All you need is to use FIELD()

ORDER BY FIELD(color, 49, 2) DESC

this will sort with Color = 2 first on the list, followed by 49 then other colors.

Upvotes: 2

Related Questions