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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2008-09-27 01:08:35 | Re: Finding sequential records |
Previous Message | Steve Midgley | 2008-09-26 22:25:59 | Re: Finding sequential records |