From mezei.zoltan@telefor.hu Thu Oct 19 11:52:03 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 276969FB219 for ; Thu, 19 Oct 2006 08:52:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 82969-07 for ; Thu, 19 Oct 2006 08:51:59 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id ED2AB9FB20F for ; Thu, 19 Oct 2006 08:51:58 -0300 (ADT) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id 1F0921C04D for ; Thu, 19 Oct 2006 13:51:55 +0200 (CEST) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Thu, 19 Oct 2006 13:51:55 +0200 Message-ID: <453766DB.6000408@telefor.hu> Date: Thu, 19 Oct 2006 13:51:55 +0200 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor Kft. User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Grouping by day, limiting amounts Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 19 Oct 2006 11:51:55.0840 (UTC) FILETIME=[F9BE6C00:01C6F374] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.292 tagged_above=0 required=5 tests=AWL, HTML_30_40, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200610/175 X-Sequence-Number: 26564 Hi,

I didn't really know what subject I should give.

I have a table like this one:

2006.10.01. Bela 10
2006.10.01. Aladar 9
2006.10.01. Cecil 8
2006.10.01. Dezso 7
2006.10.01. Elemer 6
2006.10.02. Bela 11
2006.10.02. Aladar 10
2006.10.02. Cecil 9
2006.10.02. Dezso 8
2006.10.02. Ferenc 7
2006.10.03. Bela 6
2006.10.03. Aladar 5
2006.10.03. Cecil 4
2006.10.03. Dezso 3
2006.10.03. Jozef 2

The first column is a date, the second is a name, the third is the number of votes that the name received on that day.

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?

Thanks,

Zizi
From andreas.kretschmer@schollglas.com Thu Oct 19 12:30:28 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EDC179FB255 for ; Thu, 19 Oct 2006 09:30:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 96653-05 for ; Thu, 19 Oct 2006 09:30:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.171]) by postgresql.org (Postfix) with ESMTP id B6B169FB235 for ; Thu, 19 Oct 2006 09:30:22 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu3) with ESMTP (Nemesis), id 0MKxQS-1GaX2S1ACs-0005eM; Thu, 19 Oct 2006 14:30:20 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1GaX2R-0005hG-00 for pgsql-sql@postgresql.org; Thu, 19 Oct 2006 14:30:19 +0200 Date: Thu, 19 Oct 2006 14:30:19 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Grouping by day, limiting amounts Message-ID: <20061019123018.GC10803@a-kretschmer.de> References: <453766DB.6000408@telefor.hu> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <453766DB.6000408@telefor.hu> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! User-Agent: Mutt/1.5.9i X-Provags-ID: kundenserver.de abuse@kundenserver.de login:51ea9561fc3f6d9539a2acd743e4748c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.33 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/176 X-Sequence-Number: 26565 am Thu, dem 19.10.2006, um 13:51:55 +0200 mailte Mezei Zolt�n folgendes: > Hi, > > I didn't really know what subject I should give. > > I have a table like this one: > > 2006.10.01. � �Bela � �10 > 2006.10.01. � �Aladar � �9 > 2006.10.01. � �Cecil � �8 > 2006.10.01. � �Dezso � �7 > 2006.10.01. � �Elemer � �6 > 2006.10.02. � �Bela � �11 > 2006.10.02. � �Aladar � �10 > 2006.10.02. � �Cecil � �9 > 2006.10.02. � �Dezso � �8 > 2006.10.02. �� Ferenc �� 7 > 2006.10.03. � �Bela � �6 > 2006.10.03. � �Aladar � �5 > 2006.10.03. � �Cecil � �4 > 2006.10.03. � �Dezso � �3 > 2006.10.03. �� Jozef �� 2 > > The first column is a date, the second is a name, the third is the number of > votes that the name received on that day. > > 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? I would write a Set-returning function. First, select distinct all dates, and interate over this to select the names and number of votes for this date, ordered by the votes descending and limit this to 3 oder 10 or whatever. simplified: create function .... (IN c int, OUT d date, OUT n text, OUT x int) returns setof record ... declare temp_date date; rec record; begin for select into temp_date distinct date from table loop for select into rec name, votes from table where datum=temp_date order by 2 limit by $1 loop n := rec.name; ... return next ... HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net From andreas.kretschmer@schollglas.com Thu Oct 19 14:17:56 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EEF4B9FB2D8 for ; Thu, 19 Oct 2006 11:17:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 41855-04 for ; Thu, 19 Oct 2006 11:17:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.188]) by postgresql.org (Postfix) with ESMTP id 26C809FB278 for ; Thu, 19 Oct 2006 11:17:42 -0300 (ADT) Received: from [194.25.154.138] (helo=webserv.wug.de) by mrelayeu.kundenserver.de (node=mrelayeu1) with ESMTP (Nemesis), id 0MKwpI-1GaYiH2PGX-0004Qz; Thu, 19 Oct 2006 16:17:38 +0200 Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1) id 1GaYiG-0006SS-00 for pgsql-sql@postgresql.org; Thu, 19 Oct 2006 16:17:36 +0200 Date: Thu, 19 Oct 2006 16:17:36 +0200 From: "A. Kretschmer" To: pgsql-sql@postgresql.org Subject: Re: Grouping by day, limiting amounts Message-ID: <20061019141736.GD10803@a-kretschmer.de> References: <20061019123018.GC10803@a-kretschmer.de> <20061019135541.9836.qmail@web54303.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20061019135541.9836.qmail@web54303.mail.yahoo.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! User-Agent: Mutt/1.5.9i X-Provags-ID: kundenserver.de abuse@kundenserver.de login:51ea9561fc3f6d9539a2acd743e4748c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.326 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200610/177 X-Sequence-Number: 26566 am Thu, dem 19.10.2006, um 6:55:41 -0700 mailte chester c young folgendes: > --- "A. Kretschmer" 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 where ='2006-10-10' > limit 5 order by desc; From george.woodring@iglass.net Thu Oct 19 14:51:27 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 26D139FB2C8 for ; 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 ; 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 ; 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 ; 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" To: "'A. Kretschmer'" , 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" 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

where ='2006-10-10' > limit 5 order by 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 From jim@nasby.net Thu Oct 19 16:55:42 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 98D2D9FA5D8 for ; Thu, 19 Oct 2006 13:55:42 -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 18914-10 for ; Thu, 19 Oct 2006 16:55:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 01C109FA12F for ; Thu, 19 Oct 2006 13:55:32 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id EF1E856446; Thu, 19 Oct 2006 11:55:31 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 19 Oct 2006 11:55:29 -0500 Date: Thu, 19 Oct 2006 11:55:29 -0500 From: "Jim C. Nasby" To: Mezei Zolt??n Cc: pgsql-sql@postgresql.org Subject: Re: Grouping by day, limiting amounts Message-ID: <20061019165528.GI71084@nasby.net> References: <453766DB.6000408@telefor.hu> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <453766DB.6000408@telefor.hu> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061019:mezei.zoltan@telefor.hu::uGkrpvmJCIZrW3sI:00000000000000 000000000000000000000000F1Ft X-Hashcash: 1:20:061019:pgsql-sql@postgresql.org::Z79Wum0gHfHF3g5E:0000000000000 0000000000000000000000006H74 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/180 X-Sequence-Number: 26569 On Thu, Oct 19, 2006 at 01:51:55PM +0200, Mezei Zolt??n wrote: > > Hi, > I didn't really know what subject I should give. > I have a table like this one: > 2006.10.01. Bela 10 > 2006.10.01. Aladar 9 > 2006.10.01. Cecil 8 > 2006.10.01. Dezso 7 > 2006.10.01. Elemer 6 > 2006.10.02. Bela 11 > 2006.10.02. Aladar 10 > 2006.10.02. Cecil 9 > 2006.10.02. Dezso 8 > 2006.10.02. Ferenc 7 > 2006.10.03. Bela 6 > 2006.10.03. Aladar 5 > 2006.10.03. Cecil 4 > 2006.10.03. Dezso 3 > 2006.10.03. Jozef 2 > The first column is a date, the second is a name, the third is the > number of votes that the name received on that day. > 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? It'd be easy with windowing functions, but unfortunately we don't have those... SELECT * FROM (SELECT DISTINCT date FROM table) AS dates , (SELECT date, name, votes FROM table WHERE table.date = dates.date ORDER BY votes DESC LIMIT 3 ) ; Note that this has to scan the table twice (well, the second subquery will likely use an index on date). If you have another table that has the dates in it already, you can use that instead of the first subquery. If you know that every day has a row, you could also replace the first subquery with a generate_series(). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)