Re: Finding sequential records

At 05:38 PM 9/26/2008, Oliveiros Cristina wrote:
>In-Reply-To: <20080926222618(dot)4DD3664FC01(at)postgresql(dot)org>
>References: <20080926173921(dot)EFDA164FC00(at)postgresql(dot)org>
> <396486430809261102j73869b8es6b325621bcfe1ea6(at)mail(dot)gmail(dot)com>
> <20080926222618(dot)4DD3664FC01(at)postgresql(dot)org>
>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;
>
>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.

In my specific case it turns out I only had duplicates, but there could
have been n-plicates, so your code is still correct for my use-case
(though I didn't say that in my OP).

>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 ?

I *love* your application of that formula. It's rare for me to be able
to use "real" math in SQL, so this was a pleasure to read (and
understand!)

Thanks again to Richard and Oliveiros for a truly educating experience!
I hope some others were similarly enlightened.

With gratitude,

Steve