group consecutive records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

group consecutive records

Author

Topic

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 04:02:54

I'm looking for a way to group consecutive records. I have a table called Data with two fields; a primary key 'Time' which is a datetime and 'Value' which is a decimal. See below:

----------------------------------------
Time | Value |
----------------------------------------
2/6/2007 02:05:00.333 3.8
2/6/2007 02:10:00.333 3.8
2/6/2007 02:15:00.333 9.0
2/6/2007 02:20:00.333 9.0
2/6/2007 02:25:00.333 9.0
2/6/2007 02:30:00.333 3.8
2/6/2007 02:35:00.333 3.8

I want to be able to group by min(Time) and Value, but only those records that are consecutive. So from the data set i supplied i should be able to obtain the following data:

----------------------------------------
Time | Value |
----------------------------------------
2/6/2007 02:05:00.333 3.8
2/6/2007 02:15:00.333 9.0
2/6/2007 02:30:00.333 3.8

Any ideas? I've been trying to solve this for ages.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 04:06:08

is there a primary key to the table ?

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 04:07:34

quote:
Originally posted by khtan

is there a primary key to the table ?

KH

Yep 'Time' is the primary key.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 04:13:43

sorry. somehow did not see that in the post

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 10:03:28

[code]-- prepare sample data
declare @t table (Time datetime, Value smallmoney)

insert @t
select '2/6/2007 02:05:00.333', 3.8 union all
select '2/6/2007 02:10:00.333', 3.8 union all
select '2/6/2007 02:15:00.333', 9.0 union all
select '2/6/2007 02:20:00.333', 9.0 union all
select '2/6/2007 02:25:00.333', 9.0 union all
select '2/6/2007 02:30:00.333', 3.8 union all
select '2/6/2007 02:35:00.333', 3.8

-- show the expected output
select d.time,
d.value
from (
select t1.time,
t1.value,
(select max(t2.time) from @t as t2 where t2.time < t1.time) as previous
from @t as t1
) as d
left join @t as t on t.time = d.previous
where t.time is null
or d.value <> t.value[/code]

Peter Larsson
Helsingborg, Sweden

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 11:42:45

Thank you so much for that solution. I tested it out over a large data and it takes a long while. Is there a way to speed up this query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 11:45:10

Is it guaranteed that the values are EXACTLY 5 minutes apart (to the millisecond)?
Do you also have an identity column on the table?

Peter Larsson
Helsingborg, Sweden

kramer
Starting Member

8 Posts

Posted - 2007-02-09 : 12:50:09

quote:
Originally posted by Peso

Is it guaranteed that the values are EXACTLY 5 minutes apart (to the millisecond)?
Do you also have an identity column on the table?

Peter Larsson
Helsingborg, Sweden

no it's not guarenteed that the time values are exactly 5 minutes apart. there is no identity column, but i can insert one if need be.

thank you!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 17:17:34

Can you guarantee that time values always are inserted sorted?
If so, try this for speedier select

-- prepare sample data
declare	@t table (pk int identity(1, 1) primary key, Time datetime, Value smallmoney)

insert	@t
select	'2/6/2007 02:05:00.333', 3.8 union all
select	'2/6/2007 02:10:00.333', 3.8 union all
select	'2/6/2007 02:15:00.333', 9.0 union all
select	'2/6/2007 02:20:00.333', 9.0 union all
select	'2/6/2007 02:25:00.333', 9.0 union all
select	'2/6/2007 02:30:00.333', 3.8 union all
select	'2/6/2007 02:35:00.333', 3.8

-- show the expected output
select		t1.time,
		t1.value
from		@t as t1
left join	@t as t2 on t2.pk = t1.pk - 1
where		t2.pk is null
		or t2.value <> t1.value

Peter Larsson
Helsingborg, Sweden

kramer
Starting Member

8 Posts

Posted - 2007-02-15 : 15:37:09

thankyou!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-15 : 17:46:14

There is another easier way to go, since you are using SQL Server 2005.

-- prepare sample data
declare	@t table ([time] datetime, [value] smallmoney)

insert	@t
select	'2/6/2007 02:05:00.333', 3.8 union all
select	'2/6/2007 02:10:00.333', 3.8 union all
select	'2/6/2007 02:15:00.333', 9.0 union all
select	'2/6/2007 02:20:00.333', 9.0 union all
select	'2/6/2007 02:25:00.333', 9.0 union all
select	'2/6/2007 02:30:00.333', 3.8 union all
select	'2/6/2007 02:35:00.333', 3.8

-- show the expected output
select		t1.time,
		t1.value
from		(
			SELECT	[time],
				[value],
				ROW_NUMBER() OVER (ORDER BY [time]) AS recid
			FROM	@t
		) as t1
left join	(
			SELECT	[value],
				ROW_NUMBER() OVER (ORDER BY [time]) AS recid
			FROM	@t
		) as t2 on t2.recid = t1.recid - 1
where		t2.recid is null
		or t2.[value] <> t1.[value]

Peter Larsson
Helsingborg, Sweden

kramer
Starting Member

8 Posts

Posted - 2007-02-20 : 00:07:09

ok that worked even better.

Thank you again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources