How to Order by Month Name in SQLite

Problem:

You want to sort the rows by month number, given month names (you want January to be shown first, December last).

Example:

The `birthday` table contains two columns: name and `birthday_month`. The months are given in names, not in numbers.

namebirthday_month
Ronan TishaJanuary
Angie JuliaApril
Narelle DillanApril
Purdie CaseyJanuary
Donna NellNULL
Blaze GraemeOctober

You want to sort the rows by `birthday_month`.

Solution:

```SELECT *
FROM birthday
ORDER BY
(CASE birthday_month
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END);
```

The result looks like this (the rows are sorted in ascending order by `birthday_month`):

namebirthday_month
Donna NellNULL
Purdie CaseyJanuary
Ronan TishaJanuary
Angie JuliaApril
Narelle DillanApril
Blaze GraemeOctober

Discussion:

To sort the rows by month, you need the months in numbers (not names). You can convert month names to numerical months using the `CASE WHEN` clause. After the `CASE` keyword, specify the name of the column. Then, after each `WHEN`, state the value in this column, use the `THEN` keyword, and specify the new value you'd like to assign instead of the old one. Here, the column is `birthday_month`, the current values in this column are `'January'`, `'February'`, ..., `'December`', and the new values are the numerical months `1`,` 2,` ..., `12`. After you finish converting all the values, remember to use the `END` keyword to close the `CASE WHEN` clause. Take a look:

```CASE birthday_month
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END
```

This is how you convert a month name to a month number. You can use it to sort the rows in ascending order by month – just use it in the `ORDER BY` clause.

Note that in SQLite, `NULL`s are displayed first when sorting in ascending order and last when sorting in descending order. Also, the rows with the same `birthday_month` are displayed in random order (you may see `Angie Julia` second and `Narelle Dillan` third, or `Narelle Dillan` second and `Angie Julia` third). To change the order to descending, use the `DESC` keyword after the column in the `ORDER BY` clause. Here's what the query should look like:

```SELECT *
FROM birthday
ORDER BY
(CASE birthday_month
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END) DESC;
```