CODEWITHSUNDEEP
sql-server-2008t-sql
R Lukas
R Lukas

Reputation: 47

row_number () to start at #3

I'm creating a B Record list for IRS 1099 (just in case anyone else is familiar with the process). There are two header records (T Record and A Record), which are the two first rows of the file and they need a row count as well, so when I create my B Record, I'd like it to start at row number 3 as the first two rows are fixed, but I can't get it to work in my select statement.

--Padded Row Number

select right('00000000' ++ cast(row_number () over (order by column1) as varchar (10)),8)

--Results

00000001
00000002
00000003
00000004
00000005
00000006

--would like;

00000003
00000004
00000005
00000006

Thanks,

Upvotes: 0

Views: 8186

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

The expression you need to add to isn't row_number(). The expression you need to add to is row_number () over (order by column1).

create table test (
  column1 integer 
  );

insert into test values (1),(2),(3),(4),(5);

select right('00000000' + cast((row_number ()  over (order by column1)) + 2 as varchar (10)),8)
from test;

Upvotes: 5

Related Questions