Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 26D139FB2C8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 19 Oct 2006 11:51:27 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 83664-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 19 Oct 2006 14:49:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 867679FB295
	for <pgsql-sql@postgresql.org>; Thu, 19 Oct 2006 11:45:33 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3
Received: from PHOENIX.istructure.com (unknown [24.199.154.122])
	by svr4.postgresql.org (Postfix) with ESMTP id 6DA3E5AF899
	for <pgsql-sql@postgresql.org>; Thu, 19 Oct 2006 14:45:28 +0000 (GMT)
Received: from iglassnoc3 ([192.168.177.128]) by PHOENIX.istructure.com with
	Microsoft SMTPSVC(6.0.3790.1830); Thu, 19 Oct 2006 10:45:25 -0400
From: "Woody Woodring" <george.woodring@iglass.net>
To: "'A. Kretschmer'" <andreas.kretschmer@schollglas.com>,
	<pgsql-sql@postgresql.org>
Subject: Re: Grouping by day, limiting amounts
Date: Thu, 19 Oct 2006 10:45:26 -0400
Message-ID: <041801c6f38d$37313d70$80b1a8c0@istructure.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
In-Reply-To: <20061019141736.GD10803@a-kretschmer.de>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
Thread-Index: AcbziXSpHPpYeGZDRMqG5cFHU85U0AAAzZMA
X-OriginalArrivalTime: 19 Oct 2006 14:45:25.0485 (UTC)
	FILETIME=[366031D0:01C6F38D]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200610/178
X-Sequence-Number: 26567

 
I came into this thread late, so I don't know what you schema looks like.

I have a table that has a timestamp with time zone field and other data.  I
can write a query  that give me a count per day

SELECT count(*) AS count, date_trunc( 'day', hour) AS day FROM
hist_callhealth GROUP BY day;

This will give me a count per day, to get the top X

SELECT count, day FROM (SELECT count(*) AS count, date_trunc( 'day', hour)
AS day FROM hist_callhealth GROUP BY day) AS foo ORDER BY count desc limit
X;

Hope this helps,
Woody
IGLASS Networks

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of A. Kretschmer
Sent: Thursday, October 19, 2006 10:18 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Grouping by day, limiting amounts

am  Thu, dem 19.10.2006, um  6:55:41 -0700 mailte chester c young folgendes:
> --- "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
> 
> > > I would like to select the 3 (or 10) names with the most votes for
> > each day.
> > > 
> > > Any suggestions on how can it be done easily?
> 
> Am I misunderstanding, or can you write a simple:
> 
> select * from <table> where <date>='2006-10-10'
> limit 5 order by <vote_ct> desc;

From the first mail:
"I would like to select the 3 (or 10) names with the most votes for each
day."

_FOR_ _EACH_ _DAY_.


Your query returns the values only for one day...



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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