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
