I have stumped my head - 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 2000 Forums

SQL Server Development (2000)

I have stumped my head

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 08:05:05

I have following data in a table variable

Num  Grp
---  ---
  1        --<-- 1
  3        --<-- 2
  5        --<-- 3

I want to update the Grp column to a number denoting which sequence group the number is in.
This test data

Num  Grp
---  ---
  1        --<-- 1
  3        --<-- 2
  4        --<-- 2
  5        --<-- 2

should be updated as commented.

I am completely stuck at the moment

There will never be more than 200 rows in the table variable.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-23 : 08:21:24

Need more coffee, Peso?

I tend to lean towards this simple (and dirty) method...

--data
declare @t table (Num int primary key, Grp int)
insert @t (Num)
          select 1        --<-- 1
union all select 3        --<-- 2
union all select 4        --<-- 2
union all select 5        --<-- 2

--calculation
declare @Grp int, @Num int
update @t set @Grp = case when Num = @Num + 1 then @Grp else isnull(@Grp, 0) + 1 end, @Num = Num, Grp = @Grp

select * from @t

/*results
Num         Grp         
----------- ----------- 
1           1
3           2
4           2
5           2
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 08:27:47

It is this approach I am stuck with (searched this site to find it).
I am having a little bit of problem since the test data is not necessarily inserted in order.

Num  Grp
---  ---
  1        --<-- 1
  3        --<-- 2
  5        --<-- 2
  4        --<-- 2

I must get som coffee soon.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-23 : 09:09:33

Then, maybe this...

--data
declare @t table (Num int, Grp int)
insert @t (Num)
          select 1        --<-- 1
union all select 3        --<-- 2
union all select 9        --<-- 4
union all select 5        --<-- 2
union all select 4        --<-- 2
union all select 7        --<-- 3

--calculation
declare @u table (Grp int identity(1, 1) primary key, Num int) --group start Nums
insert @u select Num from @t a where not exists (select Num from @t where Num = a.Num - 1) order by Num

declare @v table (Grp int identity(1, 1) primary key, Num int) --group end Nums
insert @v select Num from @t a where not exists (select Num from @t where Num = a.Num + 1) order by Num

update t set Grp = u.Grp from @u u inner join @v v on u.Grp = v.Grp inner join @t t on t.Num between u.Num and v.Num

select * from @t

/*results
Num         Grp         
----------- ----------- 
1           1
3           2
9           4
5           2
4           2
7           3
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-23 : 09:18:30

Here are some links which might give you other ideas to play with too...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04d8.asp

http://www.sqlservercentral.com/columnists/gsmilevski/anefficientsetbasedsolutionforislandsandgaps.asp

http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 09:26:47

or just for fun... no variables or updates...


declare @t table (Num int, Grp int)
insert @t (Num)
          select 1        --<-- 1
union all select 3        --<-- 2
union all select 9        --<-- 4
union all select 5        --<-- 2
union all select 4        --<-- 2
union all select 7        --<-- 3

--
Select Z.Num, count(Y.Num)
From @t Z
Inner Join
	(
	Select A.Num 
	From @t A
	Left Join @t B
	On A.num-1 = B.num
	Where B.num is null
	) Y
On Z.Num >= Y.Num
Group By Z.Num

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 02:52:43

Excellent Corey.
Thank you Ryan for your efforts!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources