while loop - 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

General SQL Server Forums

New to SQL Server Programming

while loop

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-15 : 10:52:05

hi

i have following code:


declare @v1  int;
declare @sub  int;
set @v1 = 1;
set @sub = 3;


declare @temp table
(val int
,ord int)

insert into @temp
select 123, 1 union all
select 323, 2 union all
select 645, 3 union all
select 412, 4 union all
select 654, 5 union all
select 745, 6 union all
select 812, 7 union all
select 954, 8 


select * from @temp

declare @i int;
set @i = 0;
select @i = count(*) from @temp

declare @step int;
set @step = 0;
set @step = (@i / 3);

declare @intg int;
set @intg = 0;

while @intg-1 <= @step
begin

 insert into new_table
	 select min(val) as min_val
		   ,max(val) as max_val
	from @temp where ord between @v1 and @sub

	set @intg = @intg + 1
	set @v1 = @v1 + @sub
	set @sub= @sub + @sub
end;

it should get results into groups by 3 numbers
val from 1st till 3rd
4th till 6th
7th till ...end

and for each of this group i should have min value and max value.

but the loop does not work the way it should :)

any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:49:19

you dont need a loop. just use


select min(val),max(val)
from @temp
group by (ord-1)/3

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2009-01-16 : 01:52:11

visakh16, thank you. but i've found the "bug".

i'm posting code again, which is now working perfect :)

best



declare @temp table
(val int
,ord int)

insert into @temp
select 123, 1 union all
select 323, 2 union all
select 645, 3 union all
select 412, 4 union all
select 654, 5 union all
select 745, 6 union all
select 812, 7 union all
select 954, 8 union all
select 12, 9 union all
select 2354, 10 union all
select 1245, 11 union all
select 5312, 12 union all
select 954, 13 union all
select 454, 14 union all
select 654, 15 union all
select 754, 16 



select * into #tk_temp from @temp

declare @v1  int;
declare @sub  int;
set @v1 = 1;
set @sub = 3;




declare @i int;
set @i = 0;
select @i = count(*) from #t_temp
print @i

declare @step int;
set @step = 0;
set @step = (@i / 3);

declare @intg int;
set @intg = 0;

while @intg-1 <= @step
begin

 insert into t_table
	 select min(val) as min_val
		   ,max(val) as max_val
	from #t_temp where ord between @v1 and @sub

	set @intg = @intg + 1
--at this step definition there was error in previous post
	set @v1 = 1 + @sub
--also here :)
	set @sub= 3 + @sub
end;


select * from #t_temp
select * from t_table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 02:48:43

what was problem with my suggestion? why still use loop?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-16 : 07:24:36

<<
but i've found the "bug".
>>

What is it?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 07:37:36

Maybe values in ord column is not sequential?

DECLARE	@Sample TABLE
	(
		Val INT,
		Ord INT
	)

INSERT	@Sample
SELECT	 123,  1 UNION ALL
SELECT	 323,  3 UNION ALL
SELECT	 645,  5 UNION ALL
SELECT	 412,  6 UNION ALL
SELECT	 654,  7 UNION ALL
SELECT	 745,  8 UNION ALL
SELECT	 812,  9 UNION ALL
SELECT	 954, 10 UNION ALL
SELECT	  12, 15 UNION ALL
SELECT	2354, 16 UNION ALL
SELECT	1245, 20 UNION ALL
SELECT	5312, 21 UNION ALL
SELECT	 954, 30 UNION ALL
SELECT	 454, 40 UNION ALL
SELECT	 654, 41 UNION ALL
SELECT	 754, 49 

SELECT		grp,
		MIN(Val) AS Lowest,
		MAX(Val) AS Highest
FROM		(
			SELECT	Val,
				1 + (ROW_NUMBER() OVER (ORDER BY Ord) - 1) / 3 AS grp
			FROM	@Sample
		) AS d
GROUP BY	grp
ORDER BY	grp

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources