Re: Formatting a month in query output

On Tue, 2005-01-25 at 11:39 -0500, Sean Davis wrote:
> > I've tried "order by month asc" as well as a few other ideas, but since
> > these are now strings they are no longer ordered chronologically but
> > alphabetically. Is there a simple way I can order these by date, or am
> > I
> > going to be stuck with formatting the date as 'YYYY-MM' to get the
> > right
> > order?
>
> Rodolfo, glad it is working for you.  Did you try order by date,
> assuming your column with dates is named date?
>

Yup:

flightlog=> select to_char(date,'Mon YYYY') as month, count(num) as num,
sum(hrs_total) as hours from flights group by month order by date asc;

ERROR:  column "flights.date" must appear in the GROUP BY clause or be
used in an aggregate function

If I just run the query, the output is ordered by the way the rows are
entered into the database. Using "order by month" orders the *text*
labels alphabetically (e.g. Apr Aug Dec Feb...). Using "order by date"
gets me an error since the date column is not used in the query.

I'm sure PostgreSQL is trying to tell me something, and this is probably
an easy one, but it's still got me stumped.

Cheers,

--
Rodolfo J. Paiz <rpaiz@simpaticus.com>