pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSome insight on the proper SQL would be appreciated
5+ messages / 3 participants
[nested] [flat]
* Some insight on the proper SQL would be appreciated
@ 2010-06-08 17:04 Aaron Burnett <aburnett@bzzagent.com>
2010-06-08 17:55 ` Re: Some insight on the proper SQL would be appreciated Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-06-08 17:58 ` Re: Some insight on the proper SQL would be appreciated Mikhail V. Puzanov <misha.puzanov@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Aaron Burnett @ 2010-06-08 17:04 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Some insight on the proper SQL would be appreciated
2010-06-08 17:04 Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
@ 2010-06-08 17:55 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-06-08 18:20 ` Re: Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2010-06-08 17:55 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Some insight on the proper SQL would be appreciated
2010-06-08 17:04 Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
2010-06-08 17:55 ` Re: Some insight on the proper SQL would be appreciated Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2010-06-08 18:20 ` Aaron Burnett <aburnett@bzzagent.com>
0 siblings, 0 replies; 5+ messages in thread
From: Aaron Burnett @ 2010-06-08 18:20 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Some insight on the proper SQL would be appreciated
2010-06-08 17:04 Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
@ 2010-06-08 17:58 ` Mikhail V. Puzanov <misha.puzanov@gmail.com>
2010-06-08 18:16 ` Re: Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
1 sibling, 1 reply; 5+ messages in thread
From: Mikhail V. Puzanov @ 2010-06-08 17:58 UTC (permalink / raw)
To: Aaron Burnett <aburnett@bzzagent.com>; +Cc: 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.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Some insight on the proper SQL would be appreciated
2010-06-08 17:04 Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
2010-06-08 17:58 ` Re: Some insight on the proper SQL would be appreciated Mikhail V. Puzanov <misha.puzanov@gmail.com>
@ 2010-06-08 18:16 ` Aaron Burnett <aburnett@bzzagent.com>
0 siblings, 0 replies; 5+ messages in thread
From: Aaron Burnett @ 2010-06-08 18:16 UTC (permalink / raw)
To: Mikhail V. Puzanov <misha.puzanov@gmail.com>; +Cc: pgsql-sql
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.
>>
>>
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-06-08 18:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-08 17:04 Some insight on the proper SQL would be appreciated Aaron Burnett <aburnett@bzzagent.com>
2010-06-08 17:55 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2010-06-08 18:20 ` Aaron Burnett <aburnett@bzzagent.com>
2010-06-08 17:58 ` Mikhail V. Puzanov <misha.puzanov@gmail.com>
2010-06-08 18:16 ` Aaron Burnett <aburnett@bzzagent.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox