CODEWITHSUNDEEP
sqlsql-server-2005
ImGreg
ImGreg

Reputation: 2983

SQL Query Create isDuplicate Column with IDs

I have a SQL Server 2005 database I'm working with. For the query I am using, I want to add a custom column that can start at any number and increment based on the row entry number.

For example, I start at number 10. Each row in my results will have an incrementing number 10, 11, 12, etc..

This is an example of the SELECT statement I would be using.

int customVal = 10;

SELECT 
    ID, customVal++ 
FROM myTable

The format of the above is clearly wrong, but it is conceptually what I am looking for.

RESULTS:

 ID     CustomColumn
 -------------------
 1         10
 2         11
 3         12
 4         13

How can I go about implementing this kind functionality?

I cannot find any reference to incrementing variables within results. Is this the case?

EDIT: The customVal number will be pulled from another table. I.e. probably do a Select statement into the customVal variable. You cannot assume the the ID column will be any usable values.

The CustomColumn will be auto-incrementing starting at the customVal.

Upvotes: 2

Views: 90

Answers (1)

EkoostikMartin
EkoostikMartin

Reputation: 6911

Use the ROW_NUMBER ranking function - http://technet.microsoft.com/en-us/library/ms186734.aspx

DECLARE @Offset INT = 9

SELECT
     ID
     , ROW_NUMBER() OVER (ORDER BY ID) + @Offset
FROM
    Table

Upvotes: 5

Related Questions