Re: Finding sequential records - Mailing list pgsql-sql

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@misuse.org>
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