Thread: 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-13ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean
| Mathews | 2010-05-07ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews |
2010-06-06ff8720d4-f808-4ee6-90e6-649872fdfa55| Janis | Bunch |
2010-03-12ff48d89b-6afe-4ba0-8539-9683bec62c89| Sandy | Yashnyk |
2010-04-25ff48d2a4-0637-4e46-9461-3dd0dc0d4d83| Kendra | Elliott |
2010-05-09ff3a2a14-2575-44d5-b40b-3780f4d2506a| Kay | Maher |
2010-04-20fef7625b-d1e1-4c1a-bc82-d35a4a6db16d| Carolyn | Woodul |
2010-04-05fef21bbb-07a0-4c84-8708-4dc41b8b770b| Laurie | Montijo |
2010-04-03feee6473-af4d-4e70-b20c-a74ba08c000f| Geneva | Anderson |
2010-04-03feb690fc-0afb-4e87-b0d1-bdb2c4603fd1| Judith | Astroff |
2010-06-05fea0f9a6-e89f-4dbd-b3fd-83efed27221f| Jennifer | Lavigne |
2010-02-09fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-03-20fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-03-27fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-03fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-10fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-17fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-25fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-05-01fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-05-08fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-05-16fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-05-22fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-05-30fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-06-06fe825a6d-6820-4282-b8e9-2e3cb9f660e8| Susan | Stowe |
2010-03-12fe825a6d-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-13ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean
| Mathews | 2010-05-07ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean | Mathews |
2010-06-06ff8720d4-f808-4ee6-90e6-649872fdfa55| Janis | Bunch |
2010-03-12ff48d89b-6afe-4ba0-8539-9683bec62c89| Sandy | Yashnyk |
2010-04-25ff48d2a4-0637-4e46-9461-3dd0dc0d4d83| Kendra | Elliott |
2010-05-09ff3a2a14-2575-44d5-b40b-3780f4d2506a| Kay | Maher |
2010-04-20fef7625b-d1e1-4c1a-bc82-d35a4a6db16d| Carolyn | Woodul |
2010-04-05fef21bbb-07a0-4c84-8708-4dc41b8b770b| Laurie | Montijo |
2010-04-03feee6473-af4d-4e70-b20c-a74ba08c000f| Geneva | Anderson |
2010-04-03feb690fc-0afb-4e87-b0d1-bdb2c4603fd1| Judith | Astroff |
2010-06-05fea0f9a6-e89f-4dbd-b3fd-83efed27221f| Jennifer | Lavigne |
2010-02-09fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-03-20fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-03-27fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-03fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-10fea0334e-4ae1-4599-b24d-9a5d8ce4fd37| John | Smith |
2010-04-17fe825a6d-6820-4282-b8e9-2e3cb9f660e8| Susan | Stowe |
2010-03-12fe825a6d-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.
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" <aburnett@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
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. > > >
Mikhail, Thank you very much. This seems to have worked perfectly. On 6/8/10 1:58 PM, "Mikhail V. Puzanov" <misha.puzanov@gmail.com> wrote: > 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. >> >> >> >
Thank you so much Oliver. This also worked perfectly. On 6/8/10 1:55 PM, "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> wrote: > 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" <aburnett@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 >