PG sql query to order by formatted date in descending order-CodePudding

I'm trying to sort monthname and year in a descending order. The date column in the table is as follows

orderdate
----------
2020-12-15
2021-01-15
2021-02-15
2021-02-20
2021-03-21
2021-03-29
2021-04-15

The expected results is

monthName
-----------
April 2021
March 2021
February 2021
January 2021
December 2020

I tried this query

select distinct to_char(orderdate::date,'FMMonth YYYY') as monthName from table 
order by to_char(orderdate::date,'FMMonth YYYY') desc;

The above query gives me the output as

monthName
-----------
March 2021
January 2021
February 2021
December 2020
April 2021

How do I get the expected output from the query.

CodePudding user response：

you should order by year first, then month as month number:

select distinct to_char(orderdate::date,'FMMonth YYYY') as monthName from table 
order by to_char(orderdate::date,'YYYY MM') desc;

sorry i'm not familiar with psql enough to know the date-format, i just guessed that MM is two-digit month.

CodePudding user response：

Problem is that you are trying to sort by string, so you get alphabetical order. What you should do:

Order by year and month
Then format date the way you want

Try this one:

select to_char(trunc_date,'FMMonth YYYY') from
(
    select distinct date_trunc('month', orderdate) as trunc_date from table order by trunc_date desc
) as cte

CodePudding user response：

Assuming that orderdate contains formatted text (which is a bad idea BTW) then

select to_char(omonth, 'FMMonth YYYY') "monthName"
from (select distinct to_date(orderdate, 'yyyy-mm') omonth from the_table) t 
order by omonth desc;

If orderdate's type is date then

select to_char(omonth, 'FMMonth YYYY') "monthName"
from (select distinct date_trunc('month', orderdate) omonth from the_table) t 
order by omonth desc;