From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Finding sequential records |
Date: | 2008-09-26 17:39:13 |
Message-ID: | 20080926173921.EFDA164FC00@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2008-09-26 18:02:25 | Re: Finding sequential records |
Previous Message | Edward W. Rouse | 2008-09-26 17:23:43 | Re: Problem with pg_connect() in PHP |