Re: Some insight on the proper SQL would be appreciated

On 6/8/2010 11:29 AM, Aaron Burnett wrote:
>
> Greetings,
>
> I hope this is the proper list for this, but I am a loss on how to achieve
> one particular set of results.
>
> 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.
>
>

Ok, here we go. Add this function:

CREATE OR REPLACE FUNCTION fifth(uid text)
RETURNS timestamp without time zone
LANGUAGE plpgsql
IMMUTABLE
AS $function$
declare
result timestamp;
begin
select signedup into result from users where usename = uid
order by signedup limit 1 offset 4;
if not found then
result = '1900-01-01';
end if;
return result;
end;
$function$;

I guessed at fieldnames and table names, so you'll have to edit as needed.

Then you can run this:

select * from users where signedup <= fifth(usename) order by usename,
signedup;

-Andy