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
