Using "WITH OVER" statement - How to start new sequence number when another group of records are started?

Question

Using WITH OVER or by using other method, how to start new sequence number for another group of records?

It is SQL Server 2005.

E.g. how to get following ouput (I am talking about RowNum column in expected output)?

Table:
id name

100 A
200 B
300 C
200 B
200 B

Expected ouput:

RowNum id
1 100
1 200
2 200
3 200
1 300

Answer 1

You're looking for PARTITION BY, Test this out.

Create table #test
( 
  i int
  )

  INSERT INTO #test
  SELECT 100 UNION ALL
  SELECT 200 UNION ALL
  SELECT 300 UNION ALL
  SELECT 200 UNION ALL
  SELECT 200

  SELECT I, ROW_NUMBER() OVER (PARTITION BY I ORDER BY i ASC) FROM #test

Answer 2

declare @MyTable table (
        id int,
        name char(1)
    )

    insert into @MyTable
        (id, name)
        values
        (100,'A')
    insert into @MyTable
        (id, name)
        values
        (200,'B ')
    insert into @MyTable
        (id, name)
        values
        (300,'C') 
    insert into @MyTable
        (id, name)
        values
        (200,'B')
    insert into @MyTable
        (id, name)
        values
        (200,'B')

    select id, row_number() over(partition by id order by id) as RowNum
        from @MyTable

Answer 3

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

Your example has no clear ORDER BY nor PARTITION BY the expected output. Both could be either id, either name. A better example would be:

Table:

id type 
1  100  
2  200  
3  300  
4  200  
5  200   

Expected:

rn id type 
1  1  100  
1  2  200  
2  4  200  
3  5  200   
1  3  300  

which would correspond to ROW_NUMBER() OVER (PARTITION BY type ORDER BY id)