Re: Some insight on the proper SQL would be appreciated - Mailing list pgsql-sql
| From | Mikhail V. Puzanov |
|---|---|
| Subject | Re: Some insight on the proper SQL would be appreciated |
| Date | |
| Msg-id | 4C0E84B1.4060004@gmail.com Whole thread Raw |
| In response to | Some insight on the proper SQL would be appreciated (Aaron Burnett <aburnett@bzzagent.com>) |
| Responses |
Re: Some insight on the proper SQL would be appreciated
|
| List | 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. > > >