0

The reply selected as the best answer in this thread was helpful. But, it seems “ORDER BY FIELD” is not supported in SQLite.

Using this example, how can I set a specific order to the rows in the Month column?

Xa1
  • 15
  • 3

1 Answers1

3

There is no ORDER BY FIELD in SQLite.
Instead you can do it with conditional ordering:

ORDER BY CASE month
    WHEN 'JAN' THEN 1
    WHEN 'FEB' THEN 2
    WHEN 'MAR' THEN 3
    WHEN 'APR' THEN 4
    WHEN 'MAY' THEN 5
    WHEN 'JUN' THEN 6
END

or by using INSTR():

ORDER BY INSTR('JAN,FEB,MAR,APR,MAY,JUN', month)

you can omit the commas or replace with any other separator.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • seams when i read the documentation SQLite [INSTR](https://www.sqlite.org/lang_corefunc.html#instr) it seams its not a total drop in replacement for MySQL's [FIELD](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field)... Seams INSTR does not work on numeric datatype where FIELD does, but i know SQLite is a loose typed database just like MySQL.. The SQLite devs most likely forgot to mention in the manual that the function does with numeric datatypes as most likely it would work. – Raymond Nijland May 26 '19 at 12:54
  • 1
    INSTR is a string function. The way I use it to simulate FIELD is just a trick. But it does work also for numeric values: `select instr('15,25,35', 25)` will return 4. – forpas May 26 '19 at 13:40
  • yes offcource the (two) input params are only strings not just like MySQL's "endless" param FIELD input.. – Raymond Nijland May 26 '19 at 13:49