Incremental values in runtime ..please help

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

Incremental values in runtime ..please help

Author

Topic

rwasim25
Starting Member

13 Posts

Posted - 2010-08-23 : 06:44:13

Hi,

I would required the result set in a way like this :

Field A -- Field B
PolA 1
PolA 2
PolA 3
PolB 1
PolB 2
PolC 1

That is the field values of B will display in the runtime against field value A and whenever the values for field A will get duplicated then field B will be incremented...

Please help.. i have been struck here since long..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-23 : 07:58:23

Use ROW_NUMBER.

Example:


DECLARE @foo TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [fieldA] VARCHAR(50)
	)

INSERT @foo ([fieldA])
          SELECT 'aaa'
UNION ALL SELECT 'aaa'
UNION ALL SELECT 'bbb'
UNION ALL SELECT 'ccc'
UNION ALL SELECT 'ccc'
UNION ALL SELECT 'ccc'

-- Show @foo
SELECT * FROM @foo

-- ROW_NUMBER
SELECT
	[fieldA]
	, ROW_NUMBER() OVER (
		PARTITION BY [fieldA]
		ORDER BY [ID]
		)
		AS [fieldB]
FROM
	@foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 08:45:48

i assume you've sql 2005 or more else ROW_NUMBER wont work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rwasim25
Starting Member

13 Posts

Posted - 2010-08-24 : 23:30:55

yes i have sql server 2005..

i can get the incremental value by the keyword counter as:
DECLARE @counter int
Set @counter = 0
UPDATE cnt1
SET @counter = counter = @counter + 1

but the problem is that i need the field B value to be populated as per field value A. I mean to say:

if field A is having PolA then field B should be 1, if again fieldA is having polA then field B should be 2. But in case the field A value is polB then field B should be 1. i need the output to be incremental for the duplicate set of values, whenever a new values is in field A then again the field value for B will start from 1.

Wasim..
Senior Test Engineer

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-25 : 04:14:50

Did you try my reply with ROW_NUMBER()?

Please post the table structure of the table. There is a primary key?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:23:08

quote:
Originally posted by rwasim25

yes i have sql server 2005..

i can get the incremental value by the keyword counter as:
DECLARE @counter int
Set @counter = 0
UPDATE cnt1
SET @counter = counter = @counter + 1

but the problem is that i need the field B value to be populated as per field value A. I mean to say:

if field A is having PolA then field B should be 1, if again fieldA is having polA then field B should be 2. But in case the field A value is polB then field B should be 1. i need the output to be incremental for the duplicate set of values, whenever a new values is in field A then again the field value for B will start from 1.

Wasim..
Senior Test Engineer

whatever Charlie has given you does the same thing. Can I ask why it didnt work for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rwasim25
Starting Member

13 Posts

Posted - 2010-08-26 : 03:31:30

hi Visakh...

It does work i have tried the below query..

ROW_NUMBER() OVER(PARTITION BY convert(varchar,CCIETEC)+'_'+LNUMCON+'_'+convert(varchar,NVRS)
order by LNUMCON)Sequence_Number,

it does work .....
thanks to both charlie and You.

Wasim..
Senior Test Engineer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 10:59:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-27 : 08:55:42

Always use length you use use convert function
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources