arch1ve arch1ve - 7 months ago 9
SQL Question

Sort SQL array based on months of string type

I have the following

SELECT
statement:

SELECT `facility_name`, `day`, `month`, `year`, `start_time`, `end_time`
FROM reservation_db
WHERE user_id = '$userID'
ORDER BY `facility_name` ASC, `year` ASC, `day` ASC, `start_time` ASC, `end_time` ASC"


It works fine, but in my table I also have a column
month
that contains the months as strings (i.e. "Ian", "Feb", "Mar", etc). Changing the type of data stored in the column is no longer an option right now.. How can I use
ORDER BY
to also sort those?

Answer

Add a case expression to you order by, where you return each month's number:

order by case `month` when 'Jan' then 1
                      when 'Feb' then 2
                      when 'Mar' then 3
                      ...
                      when 'Dec' then 12
         end