How to get results in a custom order?

Question

Instead of ASC or DESC, I want my query results to be in a specific, custom order.

For example, instead of A, B, C, D..., what if I wanted my results in, P, A, L, H...?

I have tried using case but not successfully

SELECT * FROM Customers
ORDER BY case country
when 'P' then 1 …

E.g., here, I'm trying to create a custom order on the Country column:

possible duplicate of [MySQL ORDER BY \[custom SET field value\]](http://stackoverflow.com/questions/4025675/mysql-order-by-custom-set-field-value) — Prahalad Gaggar, Oct 05 '13 at 10:10
There is no country called P. Are you looking for countries starting with that letter? — Martin Smith, Oct 05 '13 at 10:12

juergen d · Answer 1 · 2016-10-31T11:50:45.073

152

SELECT * FROM Customers
ORDER BY case when country = 'P' then 1
              when country = 'A' then 2
              when country = 'L' then 3
              when country = 'H' then 4
              else 5
         end asc

edited Oct 31 '16 at 11:50

answered Oct 05 '13 at 10:09

juergen d

201,996
37
293
362

nt the exact result.... i am sorting by country 'Mexico' then 'France'.. – mhd noufel Oct 05 '13 at 10:18
@mhdnoufel: You should add example data and expected output to your question. This is all very unclear. – juergen d Oct 05 '13 at 10:19
1

@juergend after the query ,,, i am getting the wrong result – mhd noufel Oct 05 '13 at 10:19
http://i.stack.imgur.com/BvoCT.png This is what i want , u can see the wrong result – mhd noufel Oct 05 '13 at 10:24
1

@mhdnoufel - So how do you want countries that are neither Mexico nor France sorted? – Martin Smith Oct 05 '13 at 10:28
@MartinSmith i want mexioxo first france second in the result – mhd noufel Oct 05 '13 at 10:39
4

@mhdnoufel - So add an `ELSE 3` into the `CASE`. You might also want to add `country` in as a secondary sort column. – Martin Smith Oct 05 '13 at 10:43
code please. @MartinSmith i have tried all – mhd noufel Oct 05 '13 at 11:14
@mhdnoufel: You could add a [SQLFiddle](http://sqlfiddle.com) to your querstion with all the necessary example data. Then we can fix your issue. – juergen d Oct 05 '13 at 12:19

How to get results in a custom order?

1 Answers1

Linked

Related