Solved

Add column with number of row

Posted on 2011-09-09
2
401 Views
Last Modified: 2012-05-12
I would like to add a column with the number of the row, i.e. the first row is 1, second is 2, third 3 etc.
How can I do that?
0
Comment
Question by:johnkainn
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36514343
Use ROW_NUMBER() OVER(YourColumn)

Greg
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36514378
I agree with Greg. For some clarity, please see this article:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

Please credit Greg for the solution, but you would first need to generate the row number based on some column that identifies your row as first in sort -- syntax is more precisely:
ROW_NUMBER() OVER(ORDER BY your_sort_column) RN

Open in new window


what you may be asking for though is to add an IDENTITY column. If one is not already added, you can do this: ALTER TABLE YourTable ADD ID BIGINT IDENTITY(1,1);
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

912 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

16 Experts available now in Live!

Get 1:1 Help Now