Row number in sql having issue with generating

Question

I am facing one issue in below query

CREATE TABLE #tmp(rowid int,settle_id int)
insert into #tmp
select top 100 
case when row_number() over (order by settle_id) > 10 then row_number() over (order by settle_id) - 10 else row_number() over (order by settle_id) end as rowid,settle_id from student_id(nolock) 
select * from #tmp
drop table #tmp

I want row id should start from 1 -> 10 everytime but for first two sets it start from 1->10 but there after it starts with 11. Please let me know what i am missing.

You should tag your question with the database you are using. — Gordon Linoff
– Gordon Linoff, Commented Nov 5, 2014 at 11:24
Added sql-server tag based on the #tmp and top 100 syntax — user330315
– user330315, Commented Nov 5, 2014 at 11:25

Veera · Accepted Answer · 2014-11-05 11:39:38Z

1

Use the below query to get the expected result.

SELECT 
CASE WHEN ((row_number() over(order by settle_id) % 10) = 0) 
     THEN 10 
     ELSE (row_number() over (ORDER BY settle_id) % 10)  
END AS  RowID, settle_id
FROM student

answered Nov 5, 2014 at 11:39

Veera

3,4922 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-11-05 12:40:08Z

1

Try using modulo arithmetic:

select ((row_number() over (order by settle_id) - 1) % 10) + 1 as rowid, settle_id
from student;

Some databases use the mod() function instead of %.

edited Nov 5, 2014 at 12:40

answered Nov 5, 2014 at 11:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Veera Over a year ago

Above query start the rowid from 2 only.

Nick.Mc Over a year ago

so use -1 instead of +1 - apply some basic mathematics!!

Gordon Linoff Over a year ago

@Veera . . . row_number() starts at 1, but modulo starts returning at 0. The solution is to subtract 1 and then add it back in.

Collectives™ on Stack Overflow

Row number in sql having issue with generating

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related