Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    90

    Unanswered: RowNum reset for every number of Rows

    How can I reset the number of Row_Number() if I exceed the number of rows specified.
    Example:
    Code:
    Col1    RowNum
    ----------------------
    ID1      1  
    ID1      2
    ID1      3
    ID1      4
    ID1      5
    ID1      1
    ID1      2
    ID1      3
    ID1      4
    In this example, the Row_Number will reset for every 5 items.

    Thanks in advance!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT number
         , Row_Number() OVER (ORDER BY number) As rownum
         , Row_Number() OVER (ORDER BY number) % 5 As rownum_5
         , Coalesce(NullIf(Row_Number() OVER (ORDER BY number) % 5, 0), 5) As corrected_rownum_5
    FROM   master.dbo.spt_values
    WHERE  type = 'P'
    AND    number BETWEEN 1 AND 100
    ;
    results:
    Code:
    number      rownum               rownum_5             corrected_rownum_5
    ----------- -------------------- -------------------- --------------------
    1           1                    1                    1
    2           2                    2                    2
    3           3                    3                    3
    4           4                    4                    4
    5           5                    0                    5
    6           6                    1                    1
    7           7                    2                    2
    8           8                    3                    3
    9           9                    4                    4
    10          10                   0                    5
    11          11                   1                    1
    12          12                   2                    2
    13          13                   3                    3
    14          14                   4                    4
    15          15                   0                    5
    16          16                   1                    1
    etc
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •