?
Solved

SQL Query Request

Posted on 2010-08-27
6
Medium Priority
?
221 Views
Last Modified: 2012-05-10
How do you populate an integer field with its row number.  I am interested in a query that populates a field with its row number.
0
Comment
Question by:dastaub
  • 2
  • 2
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33546468
Use ROW_NUMBER()

SELECT ID, ROW_NUMBER() OVER(ORDER BY ID) ROWNO
FROM YourTable

RowNumber will be created based on the order by column. Here I have given 'ID' as primary key field.

Raj
0
 

Author Comment

by:dastaub
ID: 33546491
i was expecting an update query?
0
 
LVL 43

Expert Comment

by:pcelba
ID: 33546517
You may populate the column immediately when you are adding it:

ALTER TABLE YourTable ADD YourNewColumn int identity(1,1)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33546520
See the attached scripts.
Raj
create table #table
(
	id int
)

insert into #table select 1 union select 4 union select 9

select * from #table

-- update query
update t
	set id = r.rowno
from #table t
	inner join 
(
	select id, row_number() over(order by id) rowno
	from #table
) r on t.id = r.id

select * from #table

drop table #table

Open in new window

0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 33546546
If the column exists already then you may do it same way and then update the existing column and drop the identity column:

ALTER TABLE YourTable ADD YourNewColumn int identity(1,1)
GO
UPDATE YourTable SET YourOldIntColumn = YourNewColumn
GO
ALTER TABLE YourTable DROP COLUMN YourNewColumn
GO

The Update with ROW_NUMBER is also possible.
0
 

Author Closing Comment

by:dastaub
ID: 33546837
I was not aware that inserting the record number was involved, so i went with an identity column.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question