DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=DeQvQZJlpySQ3MBXR3FL1eIIcqzSCzU10pJNkx8wpbqz1vRE6UZXGQEB3fgVfhY7cr
	RvijOEco1yz9AgNc4wS9/tUATyMlmMavNabUK3Za0woGMciXH5rc1b5y3VF1iSa4XPoX
	l+NKl0Az3ozgwEQRThn1JfD6B55a5ldM9Z2Sw=
Message-ID: <4C0E84B1.4060004@gmail.com>
Date: Wed, 09 Jun 2010 00:58:09 +0700
From: "Mikhail V. Puzanov" <misha.puzanov@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100515 Icedove/3.0.4
MIME-Version: 1.0
To: Aaron Burnett <aburnett@bzzagent.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Some insight on the proper SQL would be appreciated
References: <C833F069.197BE%aburnett@bzzagent.com>
In-Reply-To: <C833F069.197BE%aburnett@bzzagent.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Something very straightforward looks like this, I guess:

select * from users u
where (
     select count(*) from users u1
     where u1.username = u.username
     and u1.signedup>  u.signedup
)<  5

to get recent results. Or "u1.signedup<  u.signedup"
for the first ones. But that doesn't work in case of
non-unique values in the signedup field.


> 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.
>
>
>