Skip site navigation (1) Skip section navigation (2)

Re: Finding sequential records

From: Steve Midgley <science(at)misuse(dot)org>
To: "Oliveiros Cristina" <oliveiros(dot)cristina(at)gmail(dot)com>
Cc: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>,pgsql-sql(at)postgresql(dot)org
Subject: Re: Finding sequential records
Date: 2008-09-30 02:48:09
Message-ID: 20080930024822.4E5C237BD9E@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

pgsql-sql by date

Next:From: Richard BroersmaDate: 2008-09-30 04:50:14
Subject: Re: Finding sequential records
Previous:From: Richard BroersmaDate: 2008-09-27 01:08:35
Subject: Re: Finding sequential records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group