Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 3815F632316 for ; Tue, 8 Jun 2010 14:55:27 -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 27626-09 for ; Tue, 8 Jun 2010 17:55:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 952CF633969 for ; Tue, 8 Jun 2010 14:55:19 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id LJLCGWW4; Tue, 8 Jun 2010 18:55:17 +0100 Message-ID: <5A26680090474464864AB994E586F8DF@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "Aaron Burnett" , References: Subject: Re: Some insight on the proper SQL would be appreciated Date: Tue, 8 Jun 2010 18:55:16 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.977 tagged_above=-5 required=5 tests=BAYES_50=0.8, FILL_THIS_FORM_FRAUD_PHISH=0.334, FILL_THIS_FORM_LONG=3.404, STOX_REPLY_TYPE=0.439, T_FILL_THIS_FORM=0.01, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: **** X-Archive-Number: 201006/33 X-Sequence-Number: 34224 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