Received: from hotmail.com (law-f214.hotmail.com [209.185.130.124]) by hub.org (8.9.3/8.9.3) with SMTP id NAA32327 for ; Tue, 18 Jan 2000 13:48:53 -0500 (EST) (envelope-from oomoomi@hotmail.com) Received: (qmail 21056 invoked by uid 0); 18 Jan 2000 18:48:22 -0000 Message-ID: <20000118184822.21055.qmail@hotmail.com> Received: from 195.200.226.110 by www.hotmail.com with HTTP; Tue, 18 Jan 2000 10:48:22 PST X-Originating-IP: [195.200.226.110] From: "omid omoomi" To: scrappy@hub.org Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Ordering a date_part() query ... Date: Tue, 18 Jan 2000 10:48:22 PST Mime-Version: 1.0 Content-Type: text/plain; format=flowed Hi, Try some thing like this ( using order by clause ): SELECT ( date_part('month', stat_period) || '/' || date_part('day', stat_period) || '/' || date_part('year', stat_period) || ' ' || date_part('hour', stat_period) || ':00:00' ) as "Summary - by Day/Hour", sum(impressions) as impressions, sum(click_thru) as click_thru FROM banner_count GROUP BY "Summary - by Day/Hour" ORDER BY Date_part('day', stat_period ) , Date_part('hour',stat_period) ; Hope that helps, regards. Omid Omoomi >From: The Hermit Hacker >To: pgsql-sql@postgresql.org >Subject: [SQL] Ordering a date_part() query ... >Date: Tue, 18 Jan 2000 13:20:08 -0400 (AST) > > >I have a query that looks like: > >SELECT ( date_part('month', stat_period) || '/' || > date_part('day', stat_period) || '/' || > date_part('year', stat_period) || ' ' || > date_part('hour', stat_period) || ':00:00' ) as "Summary - by >Day/Hour", > sum(impressions) as impressions, sum(click_thru) as click_thru > FROM banner_count >GROUP BY "Summary - by Day/Hour"; > > >Works great, except that the output looks like: > >Summary - by Day/Hour|impressions|click_thru >---------------------+-----------+---------- >1/17/2000 23:00:00 | 63| 1 >1/18/2000 0:00:00 | 151| 0 >1/18/2000 10:00:00 | 348| 4 >1/18/2000 11:00:00 | 804| 1 >1/18/2000 12:00:00 | 180| 1 >1/18/2000 1:00:00 | 89| 0 >1/18/2000 2:00:00 | 112| 0 >1/18/2000 3:00:00 | 178| 0 >1/18/2000 4:00:00 | 184| 0 >1/18/2000 5:00:00 | 133| 0 >1/18/2000 6:00:00 | 119| 0 >1/18/2000 7:00:00 | 148| 0 >1/18/2000 8:00:00 | 224| 0 >1/18/2000 9:00:00 | 272| 0 > >Notice the 10/11/12/etc hour stats come before the 1am stats? > >So its sorting okay on the date, but I need to get it to d it on the time >as well.. > >I'm still playing with it, but if someone can suggest a means of doing >this, I'm all ears :) > >Thanks... > >Marc G. Fournier ICQ#7615664 IRC Nick: >Scrappy >Systems Administrator @ hub.org >primary: scrappy@hub.org secondary: >scrappy@{freebsd|postgresql}.org > > >************ > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com