Search Postgresql Archives

Re: Some insight on the proper SQL would be appreciated

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux