Wow.
Thanks to both Richard and Oliveiros.
Out of the box Oliveiros' solution does what I want but I don't
understand why!
>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;
What's going on here with the sum(id) equaling the average product of
the min and max? I gather that's to match id's with id's that are one
bigger than itself? Can anyone clarify how that is working?
Richard's sql is very interesting to me in concept - but it's not
getting me the results correctly:
>SELECT A.*
> FROM ( SELECT ID
> FROM Dummy
> GROUP BY name, fkey_id ) AS A
>INNER JOIN Dummy AS D
> ON A.id - 1 = D.id
> OR A.id + 1 = D.id;
This returns an error:
ERROR: column "dummy.id" must appear in the GROUP BY clause or be used
in an aggregate function
SQL state: 42803
I'm not sure how to setup that "from select" to produce id's without
adding id to the group by (which would cause the query to return too
many rows). Perhaps a natural join like in Oliveiros' sql would do the
job?
Thanks for any advice on either of these solutions. I'm going to learn
a lot here if someone can pound it into my head.
Thanks,
Steve
It seems to be returning any records that have sequential id's
regardless
At 11:02 AM 9/26/2008, Richard Broersma wrote:
>On Fri, Sep 26, 2008 at 10:39 AM, Steve Midgley <science@misuse.org>
>wrote:
> > drop table if exists dummy;
> > create table dummy (
> > id integer primary key,
> > name varchar(255),
> > fkey_id integer
> > )
> > ;
>
> > The system should return
> >
> > 502163
> > 502164
> > 502170
> > 502171
>
>
>--first get all of the duplicated ids
>
> SELECT id
> FROM Dummy
>GROUP BY name, fkey_id
>
>
>--Next from this list find check to see if there are any sibling
>immediate above or below it.
>
>SELECT A.*
> FROM ( SELECT ID
> FROM Dummy
> GROUP BY name, fkey_id ) AS A
>INNER JOIN Dummy AS D
> ON A.id - 1 = D.id
> OR A.id + 1 = D.id;
>
>--
>Regards,
>Richard Broersma Jr.
>
>Visit the Los Angeles PostgreSQL Users Group (LAPUG)
>http://pugs.postgresql.org/lapug
