Re: Finding sequential records

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