How to Order by Month Name in PostgreSQL or Oracle

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 TishaNULL
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 EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'));
```

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

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

Discussion:

To sort the rows by month starting from January and ending with December, you need to convert the month to a number (`January` to 1, `February` to `2`, etc.). Otherwise, you would see `December` before `January`. The TO_DATE(birthday_month, 'Month') function converts a full month name to a date in the `0001-MM-01` format. For example, you get `0001-12-01` for December.

You can now use the EXTRACT(MONTH FROM date) function to extract the month from this date value. The month will be a number between `1` and `12`.

Combining these two functions, you can get the month as a number using the following formula:

```EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month'))
```

Put this expression after `ORDER BY` to sort the rows by month. If you'd like to see the latest month first, you'll need to sort in descending order. To do this, you need to use a `DESC` keyword, like this:

```SELECT *
FROM birthday
ORDER BY EXTRACT(MONTH FROM TO_DATE(birthday_month, 'Month')) DESC;
```

Note that in PostgreSQL and Oracle, `NULL`s are displayed last when sorting in ascending order and first 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).