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

Re: Finding sequential records

From: "Oliveiros Cristina" <oliveiros(dot)cristina(at)gmail(dot)com>
To: "Steve Midgley" <science(at)misuse(dot)org>
Cc: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org, oliveiros(dot)cristina(at)marktest(dot)pt
Subject: Re: Finding sequential records
Date: 2008-09-27 00:38:08
Message-ID: f54607780809261738p16295359tf5783d66e6166f7f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Howdy, Steve.

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;


The GROUP BY clause is to associate records that have the same fkey_id and
name
The COUNT(*) > 1 eliminates the situations when there is just one.
Now, about the equality, now i am thinking and maybe it is a bazooka to kill
a fly. :)
In your table you just have duplicates? Or you may have triplicates? And
quadruplicates? And in general n-uplicates? At the time, I thought you might
have n-uplicates, so I designed the query to be as general as possible to
handle all that cases, from which duplicates are a particular case, but now
i am wondering if you don't have more than duplicates.

Well, anyway the idea is as follows
The sum of a sequence is given by first + last / 2 * n, with n = last -
first + 1, OK ?

So, if the set of ids is sequencial, its sum must equal that expression.
It's basically that.

But I am now wondering now  that I might have misunderstood what your
requests were...

If you just have duplicates, then maybe it is cleaner to substitute that
clause by something simpler, like MAX(id) - MIN(id) = 1

I dunno if I fully answered your questions, but if I didn't feel free to ask


Best, Oliveiros



>
>

-- 
We are going to have peace even if we have to fight for it. - General Dwight
D. Eisenhower

Teremos paz, nem que tenhamos de lutar por ela
- General Dwight D. Eisenhower

In response to

Responses

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-09-27 01:08:35
Subject: Re: Finding sequential records
Previous:From: Steve MidgleyDate: 2008-09-26 22:25:59
Subject: Re: Finding sequential records

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