Skip site navigation (1) Skip section navigation (2)

Re: Finding sequential records

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: <pgsql-sql(at)postgresql(dot)org>,"Steve Midgley" <science(at)misuse(dot)org>
Subject: Re: Finding sequential records
Date: 2008-09-26 18:09:24
Message-ID: 035301c92003$02243d90$ec5a3d0a@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: 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(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

In response to

pgsql-sql by date

Next:From: ries van TwiskDate: 2008-09-26 18:27:48
Subject: Re: Problem with pg_connect() in PHP
Previous:From: Richard BroersmaDate: 2008-09-26 18:02:25
Subject: Re: Finding sequential records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group