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" <aburn...@bzzagent.com>
To: <pgsql-sql@postgresql.org>
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
