From: | "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk> |
---|---|
To: | "'knut(dot)suebert(at)web(dot)de'" <knut(dot)suebert(at)web(dot)de>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: delete other similar entries with timestamp <= times |
Date: | 2002-07-31 08:31:45 |
Message-ID: | E2870D8CE1CCD311BAF50008C71EDE8E01F74945@MAIL_EXCHANGE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)suebert(at)web(dot)de [mailto:knut(dot)suebert(at)web(dot)de]
> Sent: 30 July 2002 20:23
> To: pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Henshall, Stuart - WCP | 2002-07-31 08:42:59 | Re: Very slow query |
Previous Message | Josh Berkus | 2002-07-30 22:59:56 | Re: Very slow query |