Re: Finding sequential records

<div dir="ltr">Howdy, Steve.<br /><br /> SELECT id<br /> FROM dummy a<br /> NATURAL JOIN (<br /> SELECT fkey_id,name<br
/>FROM dummy<br /> GROUP BY fkey_id,name<br /> HAVING COUNT(*) > 1 AND SUM(id) = (MAX(id) + MIN(id)) * (MAX(id) -
MIN(id)+ 1) / 2<br /> ) b<br /> ORDER BY id;<br /><br /><br />The GROUP BY clause is to associate records that have the
samefkey_id and name<br />The COUNT(*) > 1 eliminates the situations when there is just one.<br />Now, about the
equality,now i am thinking and maybe it is a bazooka to kill a fly. :)<br /> In your table you just have duplicates? Or
youmay 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
particularcase, but now i am wondering if you don't have more than duplicates.<br /><br />Well, anyway the idea is as
follows<br/>The sum of a sequence is given by first + last / 2 * n, with n = last - first + 1, OK ?<br /><br />So, if
theset of ids is sequencial, its sum must equal that expression. It's basically that.<br /><br />But I am now wondering
now that I might have misunderstood what your requests were...<br /><br />If you just have duplicates, then maybe it is
cleanerto substitute that clause by something simpler, like MAX(id) - MIN(id) = 1 <br /><br />I dunno if I fully
answeredyour questions, but if I didn't feel free to ask<br /><br /><br />Best, Oliveiros<br /><br /><div
class="gmail_quote"><br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;"><br /><br /></blockquote></div><br clear="all" /><br />-- <br />We are going to have
peaceeven if we have to fight for it. - General Dwight D. Eisenhower<br /><br />Teremos paz, nem que tenhamos de lutar
porela<br />- General Dwight D. Eisenhower<br /></div>