How about:
select to_char(mtrantime,'mm-dd hh AM') as datetime,
to_char(mtrantime,'AM') as sort_field,
count(*) as tot from memtran
group by sort_field, datetime
order by sort_field, datetime;
Then ignore the sort_field column?
Michael
"Mike Nolan" <nolan@gw.tssi.com> wrote in message
200404082349.i38NnN45017008@gw.tssi.com">news:200404082349.i38NnN45017008@gw.tssi.com...
> I'm trying to create a summary log by hour. Here's the query (somewhat
> simplified):
>
> select to_char(mtrantime,'mm-dd hh AM') as datetime,
> count(*) as tot from memtran
> group by datetime
> order by datetime;
>
> The problem is this produces the data in the following order:
>
> datetime | tot
> -------------+-----
> 04-08 01 PM | 14
> 04-08 02 PM | 15
> 04-08 03 PM | 23
> 04-08 07 AM | 8
> 04-08 08 AM | 54
> 04-08 09 AM | 30
> 04-08 10 AM | 11
> 04-08 11 AM | 10
> 04-08 11 PM | 7
> 04-08 12 PM | 10
>
> What I'd really like is to get it in chronological order by hour:
>
> 04-08 07 AM | 8
> 04-08 08 AM | 54
> 04-08 09 AM | 30
> 04-08 10 AM | 11
> 04-08 11 AM | 10
> 04-08 12 PM | 10
> 04-08 01 PM | 14
> 04-08 02 PM | 15
> 04-08 03 PM | 23
> 04-08 11 PM | 7
>
> I would prefer not to show the time of day in 24 hour format, but
> there doesn't appear to be a way to order by something that
> isn't in the select and group by clause and I don't want to display
> the hour twice.
>
> Putting the AM/PM before the HH (which looks a bit clumsy) almost works,
> except that 12PM gets sorted to the bottom after 11PM.
>
> Is there an easy way around this?
> --
> Mike Nolan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
