CODEWITHSUNDEEP
mysql
katie hudson
katie hudson

Reputation: 2893

Order By a month name

I am storing data in my database. The data being stored looks like this

id  | upload_month | created_at
-----------------------------------------
1   | Febuary      | 2017-01-30 13:22:39
-----------------------------------------
2   | January      | 2017-01-30 13:23:42
-----------------------------------------
3   | January      | 2017-01-30 13:25:33

What I needed to do was get the unique upload_months, always using the latest created_at date. So for the above I was after something like this 

id  | upload_month | created_at
-----------------------------------------
1   | Febuary      | 2017-01-30 13:22:39
-----------------------------------------
2   | January      | 2017-01-30 13:25:33
-----------------------------------------

To achieve this I have the following SQL

SELECT *
  FROM uploaded_file
  JOIN (SELECT uploaded_file.upload_month, MAX(uploaded_file.created_at) created_at
          FROM uploaded_file
      GROUP BY uploaded_file.upload_month) months
    ON uploaded_file.upload_month = months.upload_month
   AND uploaded_file.created_at = months.created_at

Now the above works well, but now I am looking to order the result of the above by the upload_month. So ideally, the above should have January first and then Febuary.

Is there any way I can order by the upload_month?

Thanks

Upvotes: 0

Views: 1233

Answers (3)

krishn Patel
krishn Patel

Reputation: 2599

you can convert month name into number and use it as order by like this.

order by month(str_to_date(upload_month,'%M'))

Upvotes: 3

Ike Walker
Ike Walker

Reputation: 65587

Assuming all you care about is getting the months in chronological order without regard to the year, then you can format the month as a date, and then sort by that.

For example:

order by str_to_date(concat(year(current_date()),'-', upload_month,'-01'),'%Y-%M-%d')

Upvotes: 3

Álvaro González
Álvaro González

Reputation: 146660

ORDER BY CASE months.upload_month
    WHEN 'January' THEN 1
    WHEN 'February' THEN 2
    -- ... You get the idea
    WHEN 'December' THEN 12
END

And next time you can store month as TINYINT to avoid this problem.

Upvotes: 3

Related Questions