Ordering columns in sql

Question

I have data in DB

---

Germany
India
USA
China
Japan
Africa

---

I want a sql query which results:

---

India (India as first row)
Africa (all the other alphabetically arranged)
Japan
USA
China (China as last row)

---

Answer 1

You can use several levels of sorting:

order by col = 'India' desc, col = 'China', col

Rationale: in MySQL, expression col = <val> returns 1 if the condition is fulfilled, else 0. So col = 'India' desc puts India first, while col = 'China' puts China last. Ties are then broken with a regular sort on the country name.

Answer 2

In MySQL, you can do:

order by (col = 'India') desc,
         (col = 'China') asc,
         col asc

Or use a case expression:

order by (case when col = 'India' then 1
               when col = 'China' then 3
               else 2
          end)
         col