Re: [SQL] Finding sequential records
On Fri, Sep 26, 2008 at 3:25 PM, Steve Midgley <[EMAIL PROTECTED]> wrote: > This returns an error: > > ERROR: column "dummy.id" must appear in the GROUP BY clause or be used in an > aggregate function > SQL state: 42803 Oops that what I get for trying air code :( This works instead: SELECT D1.* FROM Dummy AS D1 INNER JOIN Dummy AS D2 ON (D1.name,D1.fkey_id)=(D2.name,D2.fkey_id) AND (D1.id = D2.id + 1 OR D1.id = D2.id - 1 ) ORDER BY D1.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding sequential records
Howdy, Steve. SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) > 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; The GROUP BY clause is to associate records that have the same fkey_id and name The COUNT(*) > 1 eliminates the situations when there is just one. Now, about the equality, now i am thinking and maybe it is a bazooka to kill a fly. :) In your table you just have duplicates? Or you may have triplicates? And quadruplicates? And in general n-uplicates? At the time, I thought you might have n-uplicates, so I designed the query to be as general as possible to handle all that cases, from which duplicates are a particular case, but now i am wondering if you don't have more than duplicates. Well, anyway the idea is as follows The sum of a sequence is given by first + last / 2 * n, with n = last - first + 1, OK ? So, if the set of ids is sequencial, its sum must equal that expression. It's basically that. But I am now wondering now that I might have misunderstood what your requests were... If you just have duplicates, then maybe it is cleaner to substitute that clause by something simpler, like MAX(id) - MIN(id) = 1 I dunno if I fully answered your questions, but if I didn't feel free to ask Best, Oliveiros > > -- We are going to have peace even if we have to fight for it. - General Dwight D. Eisenhower Teremos paz, nem que tenhamos de lutar por ela - General Dwight D. Eisenhower
Re: [SQL] Finding sequential records
Wow. Thanks to both Richard and Oliveiros. Out of the box Oliveiros' solution does what I want but I don't understand why! SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) > 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; What's going on here with the sum(id) equaling the average product of the min and max? I gather that's to match id's with id's that are one bigger than itself? Can anyone clarify how that is working? Richard's sql is very interesting to me in concept - but it's not getting me the results correctly: SELECT A.* FROM ( SELECT ID FROM Dummy GROUP BY name, fkey_id ) AS A INNER JOIN Dummy AS D ON A.id - 1 = D.id OR A.id + 1 = D.id; This returns an error: ERROR: column "dummy.id" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803 I'm not sure how to setup that "from select" to produce id's without adding id to the group by (which would cause the query to return too many rows). Perhaps a natural join like in Oliveiros' sql would do the job? Thanks for any advice on either of these solutions. I'm going to learn a lot here if someone can pound it into my head. Thanks, Steve It seems to be returning any records that have sequential id's regardless At 11:02 AM 9/26/2008, Richard Broersma wrote: On Fri, Sep 26, 2008 at 10:39 AM, Steve Midgley <[EMAIL PROTECTED]> wrote: > drop table if exists dummy; > create table dummy ( > id integer primary key, > name varchar(255), > fkey_id integer > ) > ; > The system should return > > 502163 > 502164 > 502170 > 502171 --first get all of the duplicated ids SELECT id FROM Dummy GROUP BY name, fkey_id --Next from this list find check to see if there are any sibling immediate above or below it. SELECT A.* FROM ( SELECT ID FROM Dummy GROUP BY name, fkey_id ) AS A INNER JOIN Dummy AS D ON A.id - 1 = D.id OR A.id + 1 = D.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding sequential records
oops I noticed I forgot the having clause: > SELECT id > FROM Dummy > GROUP BY name, fkey_id Having count(*) > 1; > SELECT A.* > FROM ( SELECT ID > FROM Dummy > GROUP BY name, fkey_id HAVING count(*) > 1 ) AS A > INNER JOIN Dummy AS D > ON A.id - 1 = D.id > OR A.id + 1 = D.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with pg_connect() in PHP
--- On Fri, 9/26/08, Edward W. Rouse <[EMAIL PROTECTED]> wrote:
> From: Edward W. Rouse <[EMAIL PROTECTED]>
> Subject: Re: [SQL] Problem with pg_connect() in PHP
> To: "'pgsql-sql'"
> Date: Friday, September 26, 2008, 5:23 PM
> Can I assume the missing ‘.”’ From the end of
> PG_PASSWORD is a cut and paste error?
>
>
>
> Edward W. Rouse
>
>
>
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of James
> Kitambara
> Sent: Friday, September 26, 2008 1:22 AM
> To: pgsql-sql
> Cc: Kenichiro Arakaki; Ken Arakaki
> Subject: [SQL] Problem with pg_connect() in PHP
>
>
>
>
> Dear Members of
>
> I have installed the Apache 2.0.61, PHP 5.2.4 and
> PostgreSQL 8.1 on my local computer.
>
> All three software were successfully tested. I changed
> “;extension=php_pgsql.dll” to
>
> “extension=php_pgsql.dll”in the php.ini file in
> order to enable PostgreSQL in PHP.
>
> The problem comes when I try to connect to the PostgreSQL
> Database using php function pg_connect
>
> $dbconn = pg_connect("host=".PG_HOST_NAME."
> port=".PG_PORT_NUM."
> dbname=".PG_DB_NAME." user=".PG_USER."
> password=".PG_PASSWORD);
>
> All the arguments in the function pg_connect() are defined.
>
> Unfortunately I am getting the Fatal error: “Call to
> undefined function pg_connect() in
> C:\Web\html\Staff_Management\example1.php on
> line 23”
>
> C:\Web\html is my document root.
>
> What could be the possible mistake?
>
> Anyone to assist me!
>
> Best regards,
>
> James Kitambara
first create a file that contains this
save this with php extension, run this file from the browser and seek for the
directory extension, then look if the file php_pgsql.dll is there
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with pg_connect() in PHP
On Sep 26, 2008, at 12:23 PM, Edward W. Rouse wrote:
Can I assume the missing ‘.”’ From the end of PG_PASSWORD is a cut
and paste error?
Edward W. Rouse
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
] On Behalf Of James Kitambara
Sent: Friday, September 26, 2008 1:22 AM
To: pgsql-sql
Cc: Kenichiro Arakaki; Ken Arakaki
Subject: [SQL] Problem with pg_connect() in PHP
Dear Members of
I have installed the Apache 2.0.61, PHP 5.2.4 and PostgreSQL 8.1 on
my local computer.
All three software were successfully tested. I changed
“;extension=php_pgsql.dll” to
“extension=php_pgsql.dll”in the php.ini file in order to enable
PostgreSQL in PHP.
The problem comes when I try to connect to the PostgreSQL Database
using php function pg_connect
$dbconn = pg_connect("host=".PG_HOST_NAME." port=".PG_PORT_NUM."
dbname=".PG_DB_NAME." user=".PG_USER." password=".PG_PASSWORD);
All the arguments in the function pg_connect() are defined.
Unfortunately I am getting the Fatal error: “Call to undefined
function pg_connect() in C:\Web\html\Staff_Management\example1.php
on line 23”
C:\Web\html is my document root.
What could be the possible mistake?
Anyone to assist me!
Best regards,
James Kitambara
Did you re-start apache after you made the changes to the php.ini file?
Ries
Re: [SQL] Finding sequential records
Can this be what you need? Best, Oliveiros SELECT id FROM dummy a NATURAL JOIN ( SELECT fkey_id,name FROM dummy GROUP BY fkey_id,name HAVING COUNT(*) > 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) - MIN(id) + 1) / 2 ) b ORDER BY id; - Original Message - From: "Steve Midgley" <[EMAIL PROTECTED]> To: Sent: Friday, September 26, 2008 6:39 PM Subject: [SQL] Finding sequential records Hi, I've been kicking this around today and I can't think of a way to solve my problem in "pure SQL" (i.e. I can only do it with a looping/cursor-type solution and some variables). Given a table with this DDL/data script: drop table if exists dummy; create table dummy ( id integer primary key, name varchar(255), fkey_id integer ) ; insert into dummy (id, name, fkey_id) values (502163,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502164,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502169,'Lazy Bear Lodge',105); -- not sequential id to previous insert into dummy (id, name, fkey_id) values (502199,'Lazy Bear Lodge',105); insert into dummy (id, name, fkey_id) values (502170,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); insert into dummy (id, name, fkey_id) values (502171,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); -- not sequential id nor duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502175,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500102); insert into dummy (id, name, fkey_id) values (502213,'Sea Watch',500128); -- not duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502214,'Sea Watch',500130); Find all instances where * name is duplicated * fkey_id is the same (for the any set of duplicated name fields) * id is sequential (for any set of duplicated name fields) The system should return 502163 502164 502170 502171 Here's as far as I got: select id from dummy where name in ( select name from dummy group by name having count(name)>1 ) order by id I can't figure out how to test for duplicate fkey_id when name is the same, nor to test for sequential id's when name is the same. Having a method for either would be great, and both would be a bonus! It seems like there's a clever way to do this without cursors but I can't figure it out! Thanks for any help! Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding sequential records
On Fri, Sep 26, 2008 at 10:39 AM, Steve Midgley <[EMAIL PROTECTED]> wrote: > drop table if exists dummy; > create table dummy ( > id integer primary key, > name varchar(255), > fkey_id integer > ) > ; > The system should return > > 502163 > 502164 > 502170 > 502171 --first get all of the duplicated ids SELECT id FROM Dummy GROUP BY name, fkey_id --Next from this list find check to see if there are any sibling immediate above or below it. SELECT A.* FROM ( SELECT ID FROM Dummy GROUP BY name, fkey_id ) AS A INNER JOIN Dummy AS D ON A.id - 1 = D.id OR A.id + 1 = D.id; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Finding sequential records
Hi, I've been kicking this around today and I can't think of a way to solve my problem in "pure SQL" (i.e. I can only do it with a looping/cursor-type solution and some variables). Given a table with this DDL/data script: drop table if exists dummy; create table dummy ( id integer primary key, name varchar(255), fkey_id integer ) ; insert into dummy (id, name, fkey_id) values (502163,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502164,'3Br/3Ba Pool Villa in Westin St. John, USVI- Summer 2008',500100); insert into dummy (id, name, fkey_id) values (502169,'Lazy Bear Lodge',105); -- not sequential id to previous insert into dummy (id, name, fkey_id) values (502199,'Lazy Bear Lodge',105); insert into dummy (id, name, fkey_id) values (502170,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); insert into dummy (id, name, fkey_id) values (502171,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500089); -- not sequential id nor duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502175,'3 Bed, 1 Bath Cottage Less Than a Mile from West Dennis Beach',500102); insert into dummy (id, name, fkey_id) values (502213,'Sea Watch',500128); -- not duplicate fkey_id to previous insert into dummy (id, name, fkey_id) values (502214,'Sea Watch',500130); Find all instances where * name is duplicated * fkey_id is the same (for the any set of duplicated name fields) * id is sequential (for any set of duplicated name fields) The system should return 502163 502164 502170 502171 Here's as far as I got: select id from dummy where name in ( select name from dummy group by name having count(name)>1 ) order by id I can't figure out how to test for duplicate fkey_id when name is the same, nor to test for sequential id's when name is the same. Having a method for either would be great, and both would be a bonus! It seems like there's a clever way to do this without cursors but I can't figure it out! Thanks for any help! Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with pg_connect() in PHP
Can I assume the missing ‘.”’ From the end of PG_PASSWORD is a cut and paste
error?
Edward W. Rouse
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of James Kitambara
Sent: Friday, September 26, 2008 1:22 AM
To: pgsql-sql
Cc: Kenichiro Arakaki; Ken Arakaki
Subject: [SQL] Problem with pg_connect() in PHP
Dear Members of
I have installed the Apache 2.0.61, PHP 5.2.4 and PostgreSQL 8.1 on my local
computer.
All three software were successfully tested. I changed
“;extension=php_pgsql.dll” to
“extension=php_pgsql.dll”in the php.ini file in order to enable PostgreSQL
in PHP.
The problem comes when I try to connect to the PostgreSQL Database using php
function pg_connect
$dbconn = pg_connect("host=".PG_HOST_NAME." port=".PG_PORT_NUM."
dbname=".PG_DB_NAME." user=".PG_USER." password=".PG_PASSWORD);
All the arguments in the function pg_connect() are defined.
Unfortunately I am getting the Fatal error: “Call to undefined function
pg_connect() in C:\Web\html\Staff_Management\example1.php on line 23”
C:\Web\html is my document root.
What could be the possible mistake?
Anyone to assist me!
Best regards,
James Kitambara
Re: [SQL] pg_dump in windows
Hello Ramasubramanian, You don't have to worry about the password. You need to enter the password of the person doing the backup. One the password is provided the backup is executed. I think that anyone executing the pg_dump must be a Super user. Best Regards, James Kitambara. ORGINAL MESSAGE-- --- On Fri, 19/9/08, Ramasubramanian <[EMAIL PROTECTED]> wrote: From: Ramasubramanian <[EMAIL PROTECTED]> Subject: [SQL] pg_dump in windows To: pgsql-sql@postgresql.org Date: Friday, 19 September, 2008, 11:18 AM HI all, I am facing a problem while taking a back up in windows. It is asking for a password while using pg_dump. Why it is asking for a password ?(it is not asking password in linux) Give some details about fe_sendauth in postgres . How i can avoid asking password in windows shile taking backup? Thanks and Regards, Ramasubramanian.G|Software Engineer - Delivery E-mail:[EMAIL PROTECTED] | Extn: 1607 Sobha Renaissance Information Technology (P) Ltd. An SEI-CMM, P-CMM & SSE-CMM Level 5 Company BS ISO/IEC 27001:2005 & ISO 9001:2000 Certified A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 A Top 50 Fast Growing Technology Company (Deloitte, 2006 & 2007) | A Six Sigma Practice Company Phone: + 91 80 41951999 | Fax: + 91 80 41523300 URL: www.renaissance-it.com | Video Conference: + 91 80 4125 ?
