RowNumber when value changes - 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 2012 Forums

Transact-SQL (2012)

RowNumber when value changes

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 14:49:27

When one of the characteristic field changes, the RowNumber field should increase the number. Any input is helpful.

Input table...

CREATE TABLE ##TempTable (Number INT, Characteristic1 VARCHAR(8), Characteristic2 VARCHAR(8), Characteristic3 VARCHAR(8))

INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'b', 'c')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'r')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'r')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'z')

Output table...
CREATE TABLE ##TempTableResult (Number INT, Characteristic1 VARCHAR(8), Characteristic2 VARCHAR(8), Characteristic3 VARCHAR(8)
, RowNumber int)

INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (111, 'a', 'b', 'c', 1)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (111, 'a', 'x', 'c', 2)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (111, 'a', 'x', 'c', 2)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (111, 'a', 'x', 'c', 2)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (222, 'p', 'q', 'r', 1)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (222, 'p', 'q', 'r', 1)
INSERT INTO ##TempTableResult (Number, Characteristic1, Characteristic2, Characteristic3, RowNumber) VALUES (222, 'p', 'q', 'z', 2)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 16:04:26

Define the RowNumber as int identity(1,1) or else create a sequence object and define RowNumber as int default next value for <your sequence object>

See here for sequence objects: http://msdn.microsoft.com/en-us/library/ff878370.aspx

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 16:07:35

tried it but having hard time to pull the current row number. Having issues while pulling the data when the value changed.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 16:16:13

to pull the current row number:

1. if defined as IDENTITY:

select IDENT_CURRENT( 'table_name' )

1. if defined as sequence object:

SELECT current_value FROM sys.sequences WHERE name = 'my sequence' ;

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 16:18:38

So sorry but i am not sure what you are trying to convey here...can you please be little more clear?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 16:25:53

You said that you want to pull the current rownumber from your table. How you do it depends on how the column is defined. there are two basic ways to define the column in SS 2012:

1.

CREATE TABLE ... (...RowNumber int IDENTITY(1,1) ...)

to get the current value: select IDENT_CURRENT( 'table_name' )

2. Define a sequence object

CREATE SEQUENCE myseq AS int Minimum 1

CREATE TABLE ... (... RowNumber Int DEFAULT NEXT VALUE FOR myseq ...)

To get the current value:

SELECT current_value FROM sys.sequences WHERE name = 'myseq' ;

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 16:34:19

sorry...i still did not understand and i am not sure if you understood the question(i am sorry if you actually did). I need the RowNumber but i want to change the rowNumber only when actualy characteristic changes. Does this makes sense?

Also, consider output as a query and not a table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 17:32:02

OK so I don't know orally get your requirements. It sounds like you need a trigger that looks for specific changes and increments the rownumber when detected. Is that it?

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 17:34:22

yes it is very simple...when one of the field value changes, then the rowNumber should increase. The output is a simple SELECT statement and no need of any trigger.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 17:58:47

OK so. What happens if someone else updates your table? A trigger could handle that

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-04 : 18:05:21

The target will have new rowNumber with the latest updates. I actually have written the SELECT statement by myself in this meantime.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-05 : 05:39:58

1. You have not specified the order of the table. ie A relation is an unordered set so the order you have written them is meaningless.
I am going to assume you want ORDER BY Characteristic1, Characteristic2, Characteristic3.

2. Assuming the above, try the DENSE_RANK() windowed function:


SELECT *
	,DENSE_RANK() OVER (PARTITION BY NUMBER ORDER BY Characteristic1, Characteristic2, Characteristic3) AS RowNumber
FROM ##TempTable ;

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2014-11-05 : 11:43:56

actually lets add a timestamp field at the end for the ##TempTable and the ORDER BY should be based on TimeStamp(DATETIME). Any suggestions?

CREATE TABLE ##TempTable (Number INT, Characteristic1 VARCHAR(8), Characteristic2 VARCHAR(8), Characteristic3 VARCHAR(8), Timestamp DATETIME)

INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'b', 'c', '2014-01-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c', '2014-02-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c', '2014-03-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (111, 'a', 'x', 'c', '2014-04-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'r', '2014-01-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'r', '2014-02-01')
INSERT INTO ##TempTable (Number, Characteristic1, Characteristic2, Characteristic3) VALUES (222, 'p', 'q', 'z', '2014-03-01')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources