Post reply

SELECT based on time interval between records

  • November 11, 2008 at 4:49 am

    #202609

    Hi,

    I have a table which records the position of an object approximately every 10 seconds. I want to select a subset of these records to show the position of the object every 5 minutes (for arguments sake).

    The time intervals are not reliable - there are gaps when the object doesn't report its position or reports in 'late'.

    Does anyone have any elegant way to do this? I haven't come up with anything that doesn't involve creating a cursor and stepping through the records. My usual expert (google!!) doesn't shed much light either 🙁

    Thanks

    Tim

  • November 11, 2008 at 6:54 am

    #896917

    If you are using sequential ID values, you could take the easy way out and use an outer self join on id = id-1. That said, if there are any gaps in your ID values... you're right back in the same boat. Since you are using 2005, you can likely use ROWNUMBER to circumvent that in your query.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 11, 2008 at 6:57 am

    #896922

    That's a neat thought. I'll have to do some work on it - ID's aren't sequential because there are multiple objects putting data into the table, but I can probably work around that somehow.

    Cheers

    Tim

  • November 11, 2008 at 7:46 am

    #896953

    The requirement is a bit iffy

    But using ROW_NUMBER (in SS2005), you can create a sequence of an Object/ID

    e.g. group by ID, order by time = you have a trail of where the ID is in chronological order

    An example will be great

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply