Increment number on fields with the same value.

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

Increment number on fields with the same value.

Author

Topic

saloom
Starting Member

3 Posts

Posted - 2013-12-02 : 15:18:58

Hallo people! I am very new to SQL and I need to do something that I've been searching and searching and can't seem to find a solution. I have a Column named 'Series' and a column named 'Linenumber'. They look like this:

Series Linenumber
234 NULL
234 NULL
235 NULL
235 NULL
234 NULL
234 NULL
235 NULL
235 NULL
236 NULL
236 NULL
236 NULL

and I want to run a query and make it look like this:

Series Linenumber
234 1
234 2
235 1
235 2
234 3
234 4
235 3
235 4
236 1
236 2
236 3

So basically I want Linenumber to be an increment column but to increment seperately for each value of Series. RDBMS is Microsoft SQL Server 2008.

Do I ask too much???

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 15:26:20

This statement will simply ouput the results as you described. It won't change the data in the table. Is that what you want?


select Series
       , row_number() over (partition by series order by lineNumber) as LineNumber
from   yourTable

Be One with the Optimizer
TG

saloom
Starting Member

3 Posts

Posted - 2013-12-02 : 15:29:38

Yes I want to change the "NULL'S" into numbers. Using something like an UPDATE query.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-02 : 15:39:57

In order to do that there must be something in your table that uniquely identifies a row. If there is no way to distinguish one row that has a [Series] = 234 from a different row that has [Series]=234 then you have do something like this:


alter table yourTable add i int identity(1,1)
go

update t set
       t.lineNumber = d.lineNumber
from   (
       select i
              ,Series
              , row_number() over (partition by series order by lineNumber) as LineNumber
       from   yourTable
       ) d
inner join yourTable t
       on t.i = d.i

go
alter table #yourTable drop column i

EDIT:
fixed syntax error

Be One with the Optimizer
TG

saloom
Starting Member

3 Posts

Posted - 2013-12-02 : 16:41:55

Aaaaww this works wonders!! This is magic!! Thanks a million!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources