Adding limited Row number SQL

Question

I have the following table:

ID   GroupID   Date         Value
1    A         2014-06-01   20
2    A         2014-06-02   21
3    A         2014-06-03   22
4    B         2014-06-01   50
5    B         2014-06-02   76
6    A         2014-06-04   25
7    C         2014-06-01   70
8    A         2014-06-05   29

I want to have the following result in SQL VIEW:

ID   GroupID   Date         Value     RowNumber
1    A         2014-06-01   20        1
2    A         2014-06-02   21        2
3    A         2014-06-03   22        3
4    B         2014-06-01   50        1
5    B         2014-06-02   76        2
6    A         2014-06-04   25        4
7    C         2014-06-01   70        1
8    A         2014-06-05   29        5

But, I want to limit the RowNumber field until 24. If the number reach 24, then it will start from 1 again.

Does anyone have an idea how to do this?
Thank you.

I can see the RowNumber starts from 1 for each GroupID. Are you wanting to limit it to 24 for each GroupID? — Raj
– Raj, Commented Sep 22, 2014 at 8:36

Mehdi Haghshenas · Accepted Answer · 2014-09-22 09:26:13Z

5

you just set RowNumber Column Value to RowNumber%24+1 then when row number reached to 24 then start from 1

SELECT (ROW_NUMBER() OVER 
                     (PARTITION BY GroupID  ORDER BY ID)
                      -1)%24+1 as RowNumber,
* FROM Table

because row number to start from 1 i minus row number -1 to start from 0

edited Sep 22, 2014 at 9:26

answered Sep 22, 2014 at 8:47

Mehdi Haghshenas

2,4632 gold badges19 silver badges38 bronze badges

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

3 Comments

Haminteu Over a year ago

Hi, Thank you. Yes, that's the logic.

Mehdi Haghshenas Over a year ago

if you want I can send you sql code full select query

Haminteu Over a year ago

Yes, why not... Just edit your answer mate. Appreciate.

Sangamesh_R · Accepted Answer · 2014-09-22 10:22:44Z

0

You can achieve this using decode function available in SQL. Once the RowNumber reaches 25, RowNumber starts again from 1.

try executing below query:

select ID,GroupID,Date,Value, decode(mod(rownum,24), 0 , 24, mod(rownum,24)) mod_Val from Table

answered Sep 22, 2014 at 10:22

Sangamesh_R

12 silver badges6 bronze badges

Comments

Mehdi Haghshenas · Accepted Answer · 2014-11-27 08:47:40Z

0

this code can be helpful

with std as (select *,row_number() over(partition by groupid order by groupid,id) as RowNumber
from table1)
select * from std order by id

edited Nov 27, 2014 at 8:47

Mehdi Haghshenas

2,4632 gold badges19 silver badges38 bronze badges

answered Nov 27, 2014 at 8:41

Mohammed

3131 silver badge6 bronze badges

Collectives™ on Stack Overflow

Adding limited Row number SQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related