Some insight on the proper SQL would be appreciated

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 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