Row number or Rank beginning with a certain number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Row number or Rank beginning with a certain number

Author

Topic

texassynergy
Starting Member

26 Posts

Posted - 2010-10-26 : 17:51:28

I am just learning about Rank and Row Number. I would like to use one of them with a starting number. I am going to import data into another database and need a row number starting with a very high number. I am initially testing with the number 5000 for simplicity sake. Here is what I came up with.

DECLARE @STARTNUM INT
SET @STARTNUM = 5000
  
    
SELECT RANK=(count(*) + @STARTNUM),

My query is very lengthy, so I am limiting it to the relevant info. I return 2451 records, but the Rank column all have 5001 or 5002 in them. I tried to use something similar to Row Number, but could not make that work. Does anyone have any ideas without actually using a while or for loop? Microsoft's introduction to Row Number and Rank claim that it is a way to avoid loops.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-10-26 : 19:40:39

Select @STARTNUM + Row_Number() over (order by mysortcol) as RowID,*
from
Mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

texassynergy
Starting Member

26 Posts

Posted - 2010-10-27 : 11:28:43

YOU ROCK!!!

Thanks a bunch Vinnie881. That worked fantastically. I appreciate your input.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources