Solved

SQL Query Request

Posted on 2010-08-27
6
190 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now