469,270 Members | 1,790 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
4 7445
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" <no***@gw.tssi.com> wrote in message
news:20*************************@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 ma*******@postgresql.org

Nov 23 '05 #2
Mike Nolan <no***@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Bryan Harrington | last post: by
1 post views Thread by brett | last post: by
3 posts views Thread by Tcs | last post: by
22 posts views Thread by MP | last post: by
15 posts views Thread by Simon Gare | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.