Обсуждение: Select/Group by/Order by question
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
Mike Nolan <nolan@gw.tssi.com> writes: > 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: > ... > What I'd really like is to get it in chronological order by hour: You are grouping/ordering by the textual result of to_char(), in which PM naturally follows AM. I think the behavior you want would come from grouping/ordering by the underlying timestamp column "mtrantime". regards, tom lane
> You are grouping/ordering by the textual result of to_char(), > in which PM naturally follows AM. I think the behavior you > want would come from grouping/ordering by the underlying > timestamp column "mtrantime". Well, I need it grouped by hour, but that led me to the solution: select to_char(date_trunc('hour',mtrantime),'mm-dd hh AM') as datetime, count(*) as tot, from memtran group by mtranoper, date_trunc('hour',mtrantime) order by mtranoper, date_trunc('hour',mtrantime) I knew there had to be a straight-forward solution. Thanks Tom. -- Mike Nolan
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 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 >
> 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? I usually don't like to send managers reports with data labeled 'ignore this column'. :-) With Tom's help, I found a solution. -- Mike Nolan