[SQL] Casts in foreign schemas
Is this behaviour expected? (This is with 8.4.3 on FreeBSD.) create schema one; set search_path to one; create type foo as (x integer); create function foo (integer) returns foo language plpgsql as $$ declare y foo; begin y.x = $1; return y; end $$; create cast (integer as foo) with function foo (integer); grant usage on schema one to public; grant execute on function foo (integer) to public; create schema two; -- reconnect as a different user set search_path to two; select 3::one.foo; ERROR: type "foo" does not exist CONTEXT: compilation of PL/pgSQL function "foo" near line 2 set search_path to two, one; select 3::foo; foo - (3) (1 row) My understanding of things was that PL/pgSQL functions were compiled at CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that wrong? Is there some GRANT I'm missing that will make this work? Ben -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Casts in foreign schemas
Ben Morrow writes: > My understanding of things was that PL/pgSQL functions were compiled at > CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that > wrong? Yes. They're compiled at first use within a given session. If you want to lock down the search path used by a function, it's possible to do that in recent PG versions, by annotating the function definition with a "SET search_path = whatever" clause. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Some insight on the proper SQL would be appreciated
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" To: 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 |
Re: [SQL] Some insight on the proper SQL would be appreciated
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. -- Sent via pgsql-sql mailing list ([email protected]) To m
Re: [SQL] Some insight on the proper SQL would be appreciated
Mikhail, Thank you very much. This seems to have worked perfectly. On 6/8/10 1:58 PM, "Mikhail V. Puzanov" 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 >
Re: [SQL] Some insight on the proper SQL would be appreciated
Thank you so much Oliver. This also worked perfectly. On 6/8/10 1:55 PM, "Oliveiros d'Azevedo Cristina" 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" > To: > 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
