-1

I would like to order a MySQL query according to a certain alphabetical order. More precisely, the starting letter of the alphabet could change, depending on the webpage.

For instance, I would like to order by query based on these orders:

[B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,A] or [H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,A,B,C,D]

Let's assume my query, very simple, looks like this:

SELECT name
FROM table
ORDER BY name ASC

Would it be possible to do this only with MySQL?

Thanks a lot.

Awenig Marié
  • 193
  • 1
  • 2
  • 11
  • 2
    See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jan 13 '19 at 18:56
  • Try with a FIELD maybe? https://stackoverflow.com/questions/9378613/how-to-define-a-custom-order-by-order-in-mysql – Dharman Jan 13 '19 at 19:00
  • 1
    try `ORDER BY name BETWEEN 'B' AND 'Z' ASC, name > 'A' ASC` or `ORDER BY name BETWEEN 'B' AND 'Z' ASC, name BETWEEN 'A' AND 'A'` for `[B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,A] ` and `ORDER BY name BETWEEN 'H' AND 'Z' ASC, name BETWEEN 'A' AND 'D' ASC` for `[H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,A,B,C,D]` – Raymond Nijland Jan 13 '19 at 19:37

1 Answers1

2
SELECT * FROM my_table;
+-----+---------------+
| id  | element       |
+-----+---------------+
|  88 | Actinium      |
|  12 | Aluminium     |
|  94 | Americium     |
...
|  69 | Ytterbium     |
|  38 | Yttrium       |
|  29 | Zinc          |
|  39 | Zirconium     |
+-----+---------------+

SELECT * FROM my_table ORDER BY element <'M',element;
+-----+---------------+
| id  | element       |
+-----+---------------+
|  11 | Magnesium     |
|  24 | Manganese     |
| 108 | Meitnerium    |
...
|  81 | Lead          |
|   2 | Lithium       |
| 115 | Livermorium   |
|  70 | Lutetium      |
+-----+---------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57