[SQL] Grouping by day, limiting amounts
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
Re: [SQL] Grouping by day, limiting amounts
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
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" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Grouping by day, limiting amounts
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] [HACKERS] Bug?
See section 9.12 of the docs. On Thu, Oct 19, 2006 at 12:28:58PM +0530, Indira Muthuswamy wrote: > Then how do we clear the values of a serial column(is it done only by > dropping the column?)? > > Regards, > M.Indira > > > > On 10/19/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > >Moving to -sql. > > > >On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote: > >> Hai, > >> > >> I have encountered a problem with PostgreSQL.I have created a table > >> 'tab1' with a column 'a' with serial type.I entered 20 records into the > >> table.So the query > >> select max(a) from tab1; > >> returned 20.When I tried the same query after the command > >> truncate table tab1; > >> I found that the output of the first query as > >> > >> max > >> - > >> > >> (1 row) > >> When I tried to insert a new row into the table tab1 I found that the > >> value at column a incremented to the value 21. > >> But I heard from my friends that the value of the serial column gets > >> decremented whenever we issue the 'truncate table' command (in MS SQL > >> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me > >on > >> this? > > > >Your friends are mistaken. TRUNCATE does nothing to affect sequences. > >-- > >Jim Nasby[EMAIL PROTECTED] > >EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Grouping by day, limiting amounts
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.Bela10 > 2006.10.01.Aladar9 > 2006.10.01.Cecil8 > 2006.10.01.Dezso7 > 2006.10.01.Elemer6 > 2006.10.02.Bela11 > 2006.10.02.Aladar10 > 2006.10.02.Cecil9 > 2006.10.02.Dezso8 > 2006.10.02.Ferenc7 > 2006.10.03.Bela6 > 2006.10.03.Aladar5 > 2006.10.03.Cecil4 > 2006.10.03.Dezso3 > 2006.10.03.Jozef2 > 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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
