incrementing value column for each record

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 column for each record

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-28 : 09:48:16

Let's say I have 5 records returned by a query. How can I have a column that would have the value of 1 for the first record, 2 for the second, 3 for the third, etc?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-28 : 09:51:00

Use Row_Number in the SELECT you use to get the result...as below.

declare @t table (col1 int,col2 int)
insert @t
select 100,500 union all
select 200,1000 union all
select 300,1500

select row_number() over(order by col1) as Seq, col1 ,col2 from @t

Result
Seq col1 col2
-------------------- ----------- -----------
1 100 500
2 200 1000
3 300 1500

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 09:52:51

SELECT *, ROW_NUMBER() OVER(ORDER BY [column]) AS 'RowNumber' FROM [table]

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-28 : 10:08:12

I kind of have it working. The order by in the over part threw me off a bit. Really I wanted it ordered by seq, so I came up with this:

select row_number() over(order by policy_base) as Seq,*
from pos_vehicle
where pos_id = 18649
order by seq asc

Policy_base is the same for all records so I guess it works.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 10:11:07

quote:
Originally posted by chedderslam

I kind of have it working. The order by in the over part threw me off a bit. Really I wanted it ordered by seq, so I came up with this:

select row_number() over(order by policy_base) as Seq,*
from pos_vehicle
where pos_id = 18649
order by seq asc

Policy_base is the same for all records so I guess it works.

Then do order by your identity column.
select row_number() over(order by [your identity column] ASC) as Seq,*
from pos_vehicle
where pos_id = 18649

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 10:14:06

See what you can do with Row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-28 : 10:16:16

Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources