sql server - Select rows based on every nth interval of time -

i have table primary key (bigint), datetime, value, foreignkey configuration tabel consists of 100,000's of rows. want able obtain row variable time interval. example.

    select timestamp, value mytable configid=3      and{most recent 15 min interval} 

i have cte query returns multiple rows interval interval

    time_interval(timestamp, value, minutes)         (        select   timestamp, value, datepart(minute, timestamp)  mytable         timestamp >= '12/01/2012' , timestamp <= 'jan 10, 2013' ,         configid = 435 , (datepart(minute, timestamp) % 15) = 0     )     select timestamp, value, minutes time_interval     group minutes, value, timestamp     order timestamp 

such as:

  2012-12-19 18:15:22.040   6.98    15   2012-12-19 18:15:29.887   6.98    15   2012-12-19 18:15:33.480   7.02    15   2012-12-19 18:15:49.370   7.01    15   2012-12-19 18:30:41.920   6.95    30   2012-12-19 18:30:52.437   6.93    30   2012-12-19 19:15:18.467   7.13    15   2012-12-19 19:15:34.250   7.11    15   2012-12-19 19:15:49.813   7.12    15 

but can seen there 4 1st 15 minute interval, 2 next interval, etc... worse, if no data obtain @ exact times stamp of 15 minutes, there no value.

what want recent value fifteen minute interval... if if data intervall occurred @ 1 second after start of interval.

i thinking of lead/over again... rows not orgainzed way. primary key bigint , clustered index. both timstamp column , configid columns indexed. above query returns 4583 rows in under second.

thanks help.

try on size. handle returning 1 row instances when have multiple timestamps given interval. note: assumes bigint pk column named: idx. substitute see "idx" if not.

    ;with interval_helper([minute],minute_group)         (               select  0, 1 union select  1, 1 union select  2, 1 union select  3, 1 union select  4, 1         union select  5, 1 union select  6, 1 union select  7, 1 union select  8, 1 union select  9, 1         union select 10, 1 union select 11, 1 union select 12, 1 union select 13, 1 union select 14, 1         union select 15, 2 union select 16, 2 union select 17, 2 union select 18, 2 union select 19, 2         union select 20, 2 union select 21, 2 union select 22, 2 union select 23, 2 union select 24, 2         union select 25, 2 union select 26, 2 union select 27, 2 union select 28, 2 union select 29, 2         union select 30, 3 union select 31, 3 union select 32, 3 union select 33, 3 union select 34, 3         union select 35, 3 union select 36, 3 union select 37, 3 union select 38, 3 union select 39, 3         union select 40, 3 union select 41, 3 union select 42, 3 union select 43, 3 union select 44, 3         union select 45, 4 union select 46, 4 union select 47, 4 union select 48, 4 union select 49, 4         union select 50, 4 union select 51, 4 union select 52, 4 union select 53, 4 union select 54, 4         union select 55, 4 union select 56, 4 union select 57, 4 union select 58, 4 union select 59, 4      )     ,time_interval([timestamp], value, [date], [hour], minute_group)         (        select a.[timestamp]              ,a.value              ,convert(smalldatetime, convert(char(10), a.[timestamp], 101))              ,datepart(hour, a.[timestamp])              ,b.minute_group           mytable          join interval_helper b            on (datepart(minute, a.[timestamp])) = b.[minute]           , a.[timestamp] >= '12/01/2012'            , a.[timestamp] <= '01/10/2013'            , a.configid = 435      )     ,time_interval_timegroup([date], [hour], [minute], maxtimestamp)         (         select [date]               ,[hour]               ,minute_group               ,max([timestamp]) maxtimestamp           time_interval          group [date]               ,[hour]               ,minute_group     )     ,time_interval_timegroup_latest(maxtimestamp, maxidx)         (         select maxtimestamp               ,max(idx) maxidx           mytable           join time_interval_timegroup b             on a.[timestamp] = b.maxtimestamp          group maxtimestamp     )       select a.*       mytable       join time_interval_timegroup_latest b         on a.idx = b.maxidx      order a.[timestamp]    

---

this take on clever time group function @mntmanchris below:

create function dbo.fgettimegroup (@datepart tinyint, @date datetime) returns int begin return case @datepart             when 1 datepart(mi, @date)             when 2 datepart(mi, @date)/5 + 1   --  5 min             when 3 datepart(mi, @date)/15 + 1  -- 15 min             when 4 datepart(mi, @date)/30 + 1  -- 30 min             when 5 datepart(hh, @date)         -- hr             when 6 datepart(hh, @date)/6 + 1   -- 6 hours             when 7 datepart(hh, @date)/12 + 1  -- 12 hours             when 8 datepart(d, @date)          -- day             else -1         end end