Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 66351632350 for ; Tue, 8 Jun 2010 15:20:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 42019-04 for ; Tue, 8 Jun 2010 18:20:15 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod6og101.obsmtp.com (exprod6og101.obsmtp.com [64.18.1.181]) by mail.postgresql.org (Postfix) with SMTP id 68D196322FD for ; Tue, 8 Jun 2010 15:20:15 -0300 (ADT) Received: from source ([66.152.196.38]) by exprod6ob101.postini.com ([64.18.5.12]) with SMTP ID DSNKTA6J3OgpZYO/tEbanx6A7tv+8g7V4+hR@postini.com; Tue, 08 Jun 2010 11:20:15 PDT Received: from EXCHENT01.bzzagent.lan ([172.16.92.24]) by owa.bzzagent.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 8 Jun 2010 14:22:00 -0400 Received: from 172.16.1.85 ([172.16.1.85]) by EXCHENT01.bzzagent.lan ([172.16.92.24]) with Microsoft Exchange Server HTTP-DAV ; Tue, 8 Jun 2010 18:20:11 +0000 User-Agent: Microsoft-Entourage/12.14.0.081024 Date: Tue, 08 Jun 2010 14:20:08 -0400 Subject: Re: Some insight on the proper SQL would be appreciated From: Aaron Burnett To: Oliveiros d'Azevedo Cristina , Message-ID: Thread-Topic: [SQL] Some insight on the proper SQL would be appreciated Thread-Index: AcsHM8Lzrc6bmLbfTQGSpAq7qxNBogAA3aXM In-Reply-To: <5A26680090474464864AB994E586F8DF@marktestcr.marktest.pt> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-OriginalArrivalTime: 08 Jun 2010 18:22:00.0187 (UTC) FILETIME=[7C665CB0:01CB0737] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.156 tagged_above=-5 required=5 tests=BAYES_50=0.8, FILL_THIS_FORM_FRAUD_PHISH=0.334, RCVD_IN_DNSWL_MED=-2.3, T_FILL_THIS_FORM_SHORT=0.01 X-Spam-Level: X-Archive-Number: 201006/36 X-Sequence-Number: 34227 Thank you so much Oliver. This also worked perfectly. On 6/8/10 1:55 PM, "Oliveiros d'Azevedo Cristina" wrote: > Howdy, Aaron, > > For me this is not an easy question, specially when I don't have your data > here on my PC to test. > > But as a first approach, try this query. It is designed to give you the > oldest 5 entries. > But, be aware that this is non-tested code. Be prepared for it to not work > or even to contain sintax errors. > > Just tell me the results and we'll continue from there > > Best, > Oliver > > SELECT a.username,a.firstname,a.lastname,a.signedup > FROM t_YourTable a > JOIN t_YourTable b > ON a.username = b.username > AND a.firstname = b.firstname > AND a.lastname = b.lastname > AND a.signedup >= b.signedup > GROUP BY a.username,a.firstname,a.lastname,a.signedup > HAVING COUNT(b.*) <= 5; > > ----- Original Message ----- > From: "Aaron Burnett" > To: > Sent: Tuesday, June 08, 2010 6:04 PM > Subject: [SQL] Some insight on the proper SQL would be appreciated > > >> >> Greetings, >> >> Any help on this would be appreciated. >> >> I have a table which is a list of users who entered a contest. They can >> enter as many times as they want, but only 5 will count. So some users >> have >> one entry, some have as many as 15. >> >> How could I distill this down further to give me a list that shows each >> entry per user up to five entries per user? In other words, I need a >> separate line item for each entry from each user up to the maximum of 5 >> rows >> per user. >> >> Table looks like this: >> username | firstname | lastname | signedup >> --------------------------------------+-----------+-------------+----------- >> - >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-03-13 >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-05-07 >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-06-06 >> ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | >> 2010-03-12 >> ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | >> 2010-04-25 >> ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra | Elliott | >> 2010-05-09 >> ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | >> 2010-04-20 >> fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | >> 2010-04-05 >> fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie | Montijo | >> 2010-04-03 >> feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva | Anderson | >> 2010-04-03 >> feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith | Astroff | >> 2010-06-05 >> fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | >> 2010-02-09 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-03-20 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-03-27 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-03 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-10 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-17 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-25 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-05-01 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-05-08 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-05-16 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-05-22 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-05-30 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-06-06 >> fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | >> 2010-03-12 >> fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | >> 2010-03-15 >> >> But in John Smith's case where he has more than 5 entries, I would like >> query results to limit him to just 5 entries to look like this: >> >> username | firstname | lastname | signedup >> --------------------------------------+-----------+-------------+----------- >> - >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-03-13 >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-05-07 >> ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews | >> 2010-06-06 >> ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch | >> 2010-03-12 >> ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | >> 2010-04-25 >> ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra | Elliott | >> 2010-05-09 >> ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay | Maher | >> 2010-04-20 >> fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn | Woodul | >> 2010-04-05 >> fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie | Montijo | >> 2010-04-03 >> feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva | Anderson | >> 2010-04-03 >> feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith | Astroff | >> 2010-06-05 >> fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer | Lavigne | >> 2010-02-09 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-03-20 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-03-27 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-03 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-10 >> fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John | Smith | >> 2010-04-17 >> fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | >> 2010-03-12 >> fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe | >> 2010-03-15 >> >> The username is unique for each user. >> >> pg version 8.25 on RHEL >> >> Any help in this would be greatly appreciated. >> >> Thank you. >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >