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

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

开发者_如何学Go 1 100

1 200

2 200

3 200

1 300

---

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

---

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)

---

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