Finding start and end of sequential data groups

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)

Finding start and end of sequential data groups

Author

Topic

derach2000
Starting Member

37 Posts

Posted - 2010-07-28 : 07:05:28

Hi,
I need to get groups of sequntial data with.
Example:

1 Apple Red
2 Apple Red
3 Apple Green
4 Apple Green
5 Apple Red
6 Apple Red
7 Apple Red

The desired output would look like this:
Red apples
1-2
5-7
...

The only way I can think of is, that I take the smallest, remeber it,
find the biggest one with that has all of before it Red, and then go for the next smallest one that bigger then 2 in this example.

That would require a while loop until no more are found.

I hope I made some sence.

I don't need help with the while loop, I was wondering is there a way to write sql query that would get me the desired result.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-28 : 09:38:06

would this help you?


create table test
(id int
,name varchar(20)
)

insert into test
select 1 ,'A'
union all select 2 ,'A'
union all select 3 ,'B'
union all select 4 ,'B'
union all select 5 ,'A'
union all select 6 ,'A'
union all select 7 ,'A'


select 
	cast(cast(t1.id as varchar(5))+ '-' +cast(t2.id as varchar(5)) as varchar(20)) as range
	,t1.name
from test as t1
join test as t2
on t1.id+1 = t2.id
where t1.name = t2.name

union all

select
cast(left(x.range,1) + '-' + right(y.range,1) as varchar(10)) as range_hybrid
,y.name

from
(
select 
	cast(cast(t1.id as varchar(5))+ '-' +cast(t2.id as varchar(5)) as varchar(20)) as range
	,t1.name
from test as t1
join test as t2
on t1.id+1 = t2.id
where t1.name = t2.name
) as x
cross join  (
select 
	cast(cast(t1.id as varchar(5))+ '-' +cast(t2.id as varchar(5)) as varchar(20)) as range
	,t1.name
from test as t1
join test as t2
on t1.id+1 = t2.id
where t1.name = t2.name
) as y
where
	(y.range > x.range and x.name = y.name and right(x.range,1) = left(y.range,1))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-28 : 10:02:19

[code]DECLARE @Sample TABLE
(
Num INT,
Typ VARCHAR(40)
)

INSERT @Sample
SELECT 1, 'Apple Red' UNION ALL
SELECT 2, 'Apple Red' UNION ALL
SELECT 3, 'Apple Green' UNION ALL
SELECT 4, 'Apple Green' UNION ALL
SELECT 5, 'Apple Red' UNION ALL
SELECT 6, 'Apple Red' UNION ALL
SELECT 7, 'Apple Red'

-- Solution starts here
;WITH cteYak(Num, Typ, grp)
AS (
SELECT Num,
Typ,
Num - ROW_NUMBER() OVER (PARTITION BY Typ ORDER BY Num) AS grp
FROM @Sample
)
SELECT Typ,
MIN(Num) AS [From],
MAX(Num) AS [To]
FROM cteYak
GROUP BY grp,
Typ
ORDER BY MIN(Num)[/code]

N 56°04'39.26"
E 12°55'05.63"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-28 : 14:50:21

nice solution peso.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-29 : 02:11:02

i'm adding two more solutions :)


---#2
--- SQL 2005+

select
	 min(x.num) as od
	,max(x.num) as do
	,x.typ
from
(
	select
		num
		,typ
		,num-row_number() over (partition by typ order by num) as grp
		from @sample
) as x
group by 
		 x.grp
		,x.typ

order by 
			min(x.num)



---#3
--- SQL 2000

select
	 min(x.num) as od
	,max(x.num) as do
	,x.typ
from
(
select
t1.num
,t1.typ
,num-(select 
		count(0)+1
		from @sample as t0
			where t0.num< t1.num
			and t0.typ = t1.typ
		)  as grp
from @sample as t1
) as x
group by 
		 x.grp
		,x.typ

order by 
			min(x.num)

derach2000
Starting Member

37 Posts

Posted - 2010-07-29 : 04:10:26

Thanks guys.

Your coding style is to strong for me ;-)

I hope i'll be able to understand this and modify it to suit my needs.

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources