Solved

SQL Query Request

Posted on 2010-08-27
6
202 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 41

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 41

Accepted Solution

by:
pcelba earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

809 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