Postgres: Ordering result by to_char date field

postgresql, sql

Solution

I think the simplest way is to do:

order by min(service_date_time)

Problem

Here's my current query: ``` SELECT SUM(round("records"."value")) AS sum_value, to_char(service_date_time, 'Mon YYYY') AS to_char_service_date_time_mon_yyyy FROM "records" INNER JOIN "categories" ON "categories"."id" = "records"."category_id" WHERE (value < 10000) AND (categories.kind = 'Attendance') AND (records.service_date_time >= '2013-01-01 00:00:00.000000' AND records.service_date_time <= '2014-01-29 00:37:00.400862') GROUP BY to_char_service_date_time_mon_yyyy ``` This returns all the proper data: ``` 3036710 Aug 2013 2792991 Jul 2013 3344243 Jun 2013 3121535 Apr 2013 3752803 Sep 2013 2931149 May 2013 3046820 Nov 2013 4437698 Mar 2013 2709170 Jan 2014 3709154 Jan 2013 3361630 Dec 2013 3008767 Oct 2013 3474820 Feb 2013 ``` However, I can't seem to order by the new date format column because it's a string. If I do so, it'll sort Apr 2013 first, then Aug 2013. I need it sorted by month. I've tried a few different things, like using to_date instead of to_char, and also trying to cast the column to a date before ordering. No dice. Any ideas? Thanks!

Original source