Reputation:
I am using the following query:
SELECT distinct to_char(order_date, 'Mon') as mon from meta.ship_error order
by to_char(order_date, 'Mon') asc
and the output is:
"Apr"
"Aug"
"Dec"
"Feb"
"Jan"
"Jun"
"Mar"
"May"
"Nov"
"Oct"
"Sep"
""
but I want output like:
jan
feb
mar
apr ....
What changes should I do In my query?
Upvotes: 1
Views: 68
Reputation: 97968
You are ordering by the textual representation of the month, here:
order by to_char(order_date, 'Mon') asc
The result of to_char
is a string, so the ordering is alphabetical; Postgres no longer knows this has anything to do with dates, so has no reason to put "jan" before "apr".
You want to order by either the date itself:
order by order_date asc
Or by a numeric representation of the month, which you could get using extract()
:
order by extract(month from order_date) asc
Note that since your DISTINCT
is currently grouping based on the textual representation, you may have trouble making the SELECT
and ORDER BY
use different representations. This will be easier with an explicit GROUP BY
, which can list both representations:
group by extract(month from order_date), to_char(order_date, 'Mon')
Since you can be sure that a given order_date
can't produce a separate value for those two columns, this won't change the number of rows, but allows you to write this:
select to_char(order_date, 'Mon')
from meta.ship_error
group by extract(month from order_date), to_char(order_date, 'Mon')
order by extract(month from order_date);
Upvotes: 3
Reputation:
using this query i got my solution
SELECT distinct to_char(order_date, 'Mon') as MON , extract(month from
order_date) from meta.ship_error order by extract(month from order_date) asc
Upvotes: 0