Have a look at DISTINCT ON. It works well for this sort of thing.
On Wed, Jun 26, 2002 at 06:53:59PM -0400, Laurent Duperval wrote:
> Hello,
>
> I'm trying to do a couple of things:
>
> I have a lot of data in a database which is structured as follows:
>
> date: long
> value: numeric
> delta: numeric
>
> What happens is that sometimes the value and delta fields can be identical
> for a long period. This data is used to produce graphs so having a number of
> rows with the same value and delta but with different times just wastes space.
> How would I make sure that insertions occur only if delta and/or value are
> different than the last entry in the table?
>
> Secondly, now I've got a lot of this useless data in my table. What's the best
> way to clean it out e.g. if my data is:
>
> 1 10 1
> 2 10 1
> 3 10 1
> 4 11 2
> 5 9 0
> 6 9 0
> 7 9 0
> 8 7 -2
>
> I only want
>
> 1 10 1
> 4 11 2
> 5 9 0
> 8 7 -2
>
> left in the table. Note that I'm using the Pgtcl extension and I can script it
> in Tcl. My original idea was to do a select to get all the data in the tables
> and then go through them one by one and to remove a row if the delta/value is
> different from the previous one. But maybe there's a better way?
>
> Thanks for any ideas,
>
> L
>
> --
> Laurent Duperval <mailto:lduperval@xxxxxxxxxxxx>
>
> "I realized that the purpose of writing is to inflate ideas, obscure poor
> reasoning, and inhibit clarity."
> -Calvin
> -
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
--
Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
