pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGrouping by day, limiting amounts
5+ messages / 4 participants
[nested] [flat]
* Grouping by day, limiting amounts
@ 2006-10-19 11:51 Mezei Zoltán <mezei.zoltan@telefor.hu>
0 siblings, 2 replies; 5+ messages in thread
From: Mezei Zoltán @ 2006-10-19 11:51 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000099">
<font size="-1"><font face="Tahoma">Hi,<br>
<br>
I didn't really know what subject I should give.<br>
<br>
I have a table like this one:<small><br>
<br>
</small></font></font><small><font face="Tahoma">2006.10.01. Bela
10<br>
2006.10.01. Aladar 9<br>
2006.10.01. Cecil 8<br>
2006.10.01. Dezso 7<br>
2006.10.01. Elemer 6<br>
2006.10.02. Bela 11<br>
2006.10.02. Aladar 10<br>
2006.10.02. Cecil 9<br>
2006.10.02. Dezso 8<br>
2006.10.02. Ferenc 7<br>
2006.10.03. Bela 6<br>
2006.10.03. Aladar 5<br>
2006.10.03. Cecil 4<br>
2006.10.03. Dezso 3<br>
2006.10.03. Jozef 2<br>
<br>
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.<br>
<br>
I would like to select the 3 (or 10) names with the most votes for each
day.<br>
<br>
Any suggestions on how can it be done easily?<br>
<br>
Thanks,<br>
<br>
Zizi<br>
</font></small>
</body>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping by day, limiting amounts
@ 2006-10-19 12:30 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Mezei Zoltán <mezei.zoltan@telefor.hu>
1 sibling, 1 reply; 5+ messages in thread
From: A. Kretschmer @ 2006-10-19 12:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping by day, limiting amounts
@ 2006-10-19 14:17 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 5+ messages in thread
From: A. Kretschmer @ 2006-10-19 14:17 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping by day, limiting amounts
@ 2006-10-19 14:45 Woody Woodring <george.woodring@iglass.net>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 5+ messages in thread
From: Woody Woodring @ 2006-10-19 14:45 UTC (permalink / raw)
To: 'A. Kretschmer' <andreas.kretschmer@schollglas.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping by day, limiting amounts
@ 2006-10-19 16:55 Jim C. Nasby <jim@nasby.net>
parent: Mezei Zoltán <mezei.zoltan@telefor.hu>
1 sibling, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-10-19 16:55 UTC (permalink / raw)
To: Mezei Zolt??n <mezei.zoltan@telefor.hu>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-10-19 16:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-19 11:51 Grouping by day, limiting amounts Mezei Zoltán <mezei.zoltan@telefor.hu>
2006-10-19 12:30 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-19 14:17 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-19 14:45 ` Woody Woodring <george.woodring@iglass.net>
2006-10-19 16:55 ` Jim C. Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox