Re: 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" <science(at)misuse(dot)org>
To: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql