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: <pgsql-sql@postgresql.org>
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
