Grandhi Manikanta Grandhi Manikanta - 1 year ago 39
SQL Question

Need MySql query that should return result by ordering values in particular field

Need MySql query that should return result by ordering values in particular field.

From below my result set should contain order like parent_id (1,4,6) should come first parent_id(2,3,7) come next and other should come last.

d data parent_id
----------------------
1 a1 1
2 abc 3
3 abcd 4
4 xyz 2
5 zxyy 6
2 abc 8
3 abcd 9
4 xyz 2
5 zxyy 15

Answer Source

Use a CASE expression in your ORDER BY clause:

SELECT d, data, parent_id
FROM yourTable
ORDER BY CASE WHEN parent_id IN (1, 4, 6) THEN 1
              WHEN parent_id IN (2, 3, 7) THEN 2
              ELSE 3 END,
         parent_id

Follow the link below for a running demo:

SQLFiddle