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.
Problem is that you are trying to sort by string, so you get alphabetical order. What you should do:
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
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.
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;