refresh count - 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)

refresh count

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-08 : 07:13:04

[code]
CREATE TABLE #TABLE01(
Col1 INT)

DECLARE @x INT
SET @x = 1

WHILE @x < 11
BEGIN
INSERT INTO #TABLE01 VALUES(@x)
SET @x = @x + 1
END

SELECT * FROM #TABLE01
DROP TABLE #TABLE01
[/code]
hi how to achieve this?
Col1 (No Column Name)
1 1
2 2
3 3
4 1
5 2
6 3
7 1
8 2
9 3
10 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 07:17:35

[code]
SELECT Col1, ((Col1 - 1) % 3) + 1
FROM #TABLE01
[/code]

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-08 : 07:19:55

>"< i waste so much time thinking how to do it with row_number....anyway thx mr.tan...just learn something ^^

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 07:20:43

[code]
select NUMBER,
mtd1 = ((NUMBER - 1) % 3) + 1,
mtd2 = row_number() over (partition by (NUMBER - 1) / 3 order by NUMBER)
from F_TABLE_NUMBER_RANGE (1, 10)
order by NUMBER
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 10:09:40

quote:
Originally posted by waterduck

CREATE TABLE #TABLE01(
Col1 INT)

DECLARE @x INT
SET @x = 1

WHILE @x < 11
BEGIN
INSERT INTO #TABLE01 VALUES(@x)
SET @x = @x + 1
END

SELECT * FROM #TABLE01
DROP TABLE #TABLE01

hi how to achieve this?
Col1 (No Column Name)
1 1
2 2
3 3
4 1
5 2
6 3
7 1
8 2
9 3
10 1

Answered to your other thread

declare @t table(n int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
select n,case when sno%3=0 then 3 else sno%3 end as seqno from
(
select n,row_number() over (order by (select 1)) as sno from @t
) as t

select n,case when n%3=0 then 3 else n%3 end as seqno from @t

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 10:20:40

quote:
Originally posted by khtan

SELECT Col1, ((Col1 - 1) % 3) + 1
FROM #TABLE01

KH
[spoiler]Time is always against us[/spoiler]

Wont work for non sequence numbers

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-08 : 21:34:14

hehe...thx madhi and tan ^^...always remember this 2 method as the solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources