Solved

SQL Query Request

Posted on 2010-08-27
6
216 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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 42

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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