Re: delete other similar entries with timestamp <= times - Mailing list pgsql-novice

From Henshall, Stuart - WCP
Subject Re: delete other similar entries with timestamp <= times
Date
Msg-id E2870D8CE1CCD311BAF50008C71EDE8E01F74945@MAIL_EXCHANGE
Whole thread Raw
Responses Re: delete other similar entries with timestamp <= times
List pgsql-novice

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)
>

pgsql-novice by date:

Previous
From: Josh Berkus
Date:
Subject: Re: Very slow query
Next
From: "Henshall, Stuart - WCP"
Date:
Subject: Re: Very slow query