SELECT a.* FROM foo a INNER JOIN foo b ON b.id = a.id AND b.ts >= a.ts + '5 minutes' GROUP BY a.id, a.ts ORDER BY a.id, a.ts
Not clear yet on the needs, but this may give a start. ----- Reply message ----- From: lists-pg...@useunix.net Date: Sat, Jun 4, 2011 4:15 pm Subject: [SQL] selecting records X minutes apart To: <pgsql-sql@postgresql.org> On Sat, Jun 04, 2011 at 11:45:08AM +0000, Jasen Betts wrote: > On 2011-06-03, lists-pg...@useunix.net <lists-pg...@useunix.net> wrote: > > > > ID TS (HH:MM) > > ------------------- > > 0 20:00 > > 0 20:05 > > 0 20:10 > > 1 20:03 > > 1 20:09 > > > > > > Does my question make sense? > > no, why is (1,20:04) excluded, but (0,20:05) included? > both records are 5 minutes from the newest. Jasen, (1,20:04) is excluded because it's timestamp is less than 5 minutes from the previous record with the same ID (1,20:03), (0,20:05) is included for the opposite reason. Let me restate my requirement again with a little more detail. I want to select records grouped by ID, ordered by timestamp, in ascending order so I'm starting with the oldest, that are at least X minutes apart. I hope that helps. Thanks again, Wayne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
