CODEWITHSUNDEEP
sqlsql-serverrow-number
Bokambo
Bokambo

Reputation: 4480

Row number in sql having issue with generating

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.

Upvotes: 0

Views: 95

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

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 %.

Upvotes: 1

Veera
Veera

Reputation: 3492

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

Upvotes: 1

Related Questions