Thread: Some insight on the proper SQL would be appreciated
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.
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. > is it possible to add a new column: "isValid integer" (or something like it, to signify which one's can and cannot be selected) I was thinking of doing it in two steps, an update to set isValid, then a select with just "isValid = 1". Not sure how hard the update would be though. Oh, wait, I'll bet a window function would be helpful... but you are on 8.2 so I dont think you have them. I can think of a stored proc that might work, that ok? Oh, another thought.. if we order it by username, signedup, and then did something like: where ... signedup > (select the 5th signedup from users ... ) not 100% how to do the subselect though. I can see min(signedup) or max(signedup), but not sure how to get the 5th. Not real helpful, sorry, just some random thoughts -Andy
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
>> 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 > On 6/8/2010 2:08 PM, Aaron Burnett wrote: > > thanks very much Andy. Very elegant. > > I do need to presere the users that have<5 entries though, so I think I can > modify your function to do that as well. > > Thanks again. > > > On 6/8/10 2:50 PM, "Andy Colson"<andy@squeakycode.net> wrote: > yeah, they should show up. If a username has < 5 rows, the function returns date '1900-01-01' and the query comes out: select * from users where signedup <= '1900-01-01' order by usename, signedup; ... of course, I didnt test this too much, it just "should" do it :-) -Andy
On 6/8/2010 2:08 PM, Aaron Burnett wrote: > > thanks very much Andy. Very elegant. > > I do need to presere the users that have<5 entries though, so I think I can > modify your function to do that as well. > > Thanks again. > > Oh, duh! because nothing is less than 1900-01-01... my date math sucks. It should probably return '2100-01-01' or something. -Andy
Andy Colson <andy@squeakycode.net> wrote: >> thanks very much Andy. Very elegant. >> I do need to presere the users that have<5 entries though, so I think I can >> modify your function to do that as well. > Oh, duh! because nothing is less than 1900-01-01... my > date math sucks. It should probably return '2100-01-01' or > something. If you're using stored functions, you could (and should be- cause the whole table is probably read anyhow) also code a function that reads all entries, resets a counter at the start and whenever the user changes, increments it on every row and returns all rows where the counter is less than five. Tim
Plenty of solutions here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ -Ognjen On 8.6.2010 18:29, 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. > >
On 8 June 2010 17:29, Aaron Burnett <aburnett@bzzagent.com> 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. > Bit crude, but if you have an id column, try: SELECT username, firstname, lastname, signedup FROM entries WHERE id IN (SELECT id FROM entries limitedentries WHERE limitedentries.username = entries.username ORDER BY signedup limit 5) ORDER BY username, signedup Regards Thom
In article <4C0F4BA8.3040805@gmail.com>, Ognjen Blagojevic <ognjen.d.blagojevic@gmail.com> writes: > Plenty of solutions here: > http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ This doesn't mention the incredibly powerful windowing functions of PostgreSQL >= 8.4.0: SELECT username, firstname, lastname, signedup FROM ( SELECT username, firstname, lastname, signedup, row_number() OVER (PARTITION BY username ORDER BY signedup) FROM mytbl ) dummy WHERE row_number <= 5