Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: delete other similar entries with timestamp <= times

Re: delete other similar entries with timestamp <= times

From
"Henshall, Stuart - WCP"
Date:

Hello,
        How about something like:
DELETE FROM tbl WHERE EXISTS
        (SELECT * FROM tbl AS tbl_b WHERE tbl_b.nr=tbl.nr
                AND tbl_b.stamp<tbl.stamp AND
                tbl_b.stamp>=tbl.stamp-CAST('15 minute' AS interval));
This may have a problem with the following however:
        nr | stamp
        ---+------
         1 | 20:45
         1 | 20:55
         1 | 21:05
I am unsure wether this will leave just the 20:45 case or be indeterminate (with the alternate leaving the 20:45 and 21:05 entry)

hth,
- Stuart

> -----Original Message-----
> From: knut.suebert@web.de [mailto:knut.suebert@web.de]
> Sent: 30 July 2002 20:23
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] delete other similar entries with timestamp <=
> timestamp+15min?
>
>
> Hello,
>
> I don't know how to do the "pseudo-delete" in the middle:
>
> select * from table order by stamp;
>
>    nr | stamp
>   ----+------
>     1 | 20:45
>     1 | 20:46
>     2 | 21:00
>     1 | 21:03
>     3 | 22:05
>     2 | 22:05
>     1 | 22:06
>     1 | 22:06
>
> "delete from table where nr is not unique during stamp + '15
> minutes';"
>
> which should end in a table like
>
>    nr | stamp
>   ----+------
>     1 | 20:45
>     2 | 21:00
>     1 | 21:03
>     3 | 22:05
>     2 | 22:05
>     1 | 22:06
>
> Is that possible in SQL?
>
> Thanks,
> Knut Sübert
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

Re: delete other similar entries with timestamp <= times

From
"'knut.suebert@web.de'"
Date:
Henshall, Stuart - WCP schrieb:
>    Hello,
>            How about something like:
>    DELETE FROM tbl WHERE EXISTS
>            (SELECT * FROM tbl AS tbl_b WHERE tbl_b.nr=tbl.nr
>                    AND tbl_b.stamp<tbl.stamp AND
>                    tbl_b.stamp>=tbl.stamp-CAST('15 minute' AS interval));
>    This may have a problem with the following however:
>            nr | stamp
>            ---+------
>             1 | 20:45
>             1 | 20:55
>             1 | 21:05
>    I am unsure wether this will leave just the 20:45 case or be indeterminate
>    (with the alternate leaving the 20:45 and 21:05 entry)

That does not care a very lot in my case - but thanks a lot,
Knut Sübert