Re: delete other similar entries with timestamp <= times

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