Incrementing value - 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)

Incrementing value

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-06 : 21:19:14

/*
I have a table with random values
*/
declare @Tmp(Val varchar(30))

insert into @Tmp(val)
select 'fadsfadfada'
union all
select 'fadsfdfaa'
union all
select 'fadsfsfda'
union all
select 'fadr32fda'
/*
so now I have 4 unique values

What I want to do is pass a variable containing a integer
*/

declare @Myint int
set @MyInt = 13321

/* I then want to select all records in @Tmp order alpha and # according to MyInt*/

/*

'fadsfadfada',13321
'fadsfdfaa',13322
'fadsfsfda',13323
'fadt32fda',13324

I was thinking of doing this with a identity column and just reseeding it each time, but I was hoping there was a better way
*/

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-06 : 21:43:48

I just settled on doing this

Declare @MyInt int
set @MyInt = 12312
set @MyInt = @Myint - 1
select *,@MyInt + (Select Count(*) from @Tmp b where b.val <= a.Val)
from @Tmp a

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 22:33:35

[code]DECLARE @Tmp TABLE (Val varchar(30))

INSERT INTO @Tmp(Val)
SELECT 'fadsfadfada' UNION ALL
SELECT 'fadsfdfaa' UNION ALL
SELECT 'fadsfsfda' UNION ALL
SELECT 'fadr32fda'

DECLARE @MyInt int
SET @MyInt = 13321

SELECT Val, @MyInt + (row_number() OVER( ORDER BY Val)) - 1
FROM @Tmp[/code]

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-07-06 : 22:38:47

Can I use that method when I have a lot of criteria. Here is my exact query I am using now



Update a

set a.CheckNo =				@StartingCheckNo + (select count(*) 
							 from 
								TSTD_ShortTermDisabilityCheck aa 
							Inner Join 
								TSTD_SHortTermDisability bb  
							on		aa.ShortTermDisabilityID = bb.ShortTermDisabilityID 
							where	 
									bb.BenefitTypeID = b.BenefitTypeID 
							and		aa.ShortTermDisabilityCheckID <= a.ShortTermDisabilityCheckID 
							and		aa.checkStatusID = a.CheckStatusID)
from TSTD_ShortTermDisabilityCheck a
inner Join TSTD_SHortTermDisability b
on	a.ShortTermDisabilityID = b.ShortTermDisabilityID 
where b.BenefitTypeID = 2 
and	  a.CheckStatusID = 2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 23:12:58

Yes you can make use of Row_number() function
Also where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources