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