CODEWITHSUNDEEP
sqlsql-server-2008
Saw Al Gor
Saw Al Gor

Reputation: 91

How to set row number()

I have a table like this - 

C1   C2         C3
A   20130101    10
A   20130102    10
A   20130103    20
A   20130104    10

I want to set row no like this -

C1   C2         C3   RowNo
A   20130101    10   1
A   20130102    10   2
A   20130103    20   1
A   20130104    10   1

How can I make by query? or there is only way to loop this table?

Thanks..

Upvotes: 1

Views: 86

Answers (3)

ksa
ksa

Reputation: 403

I am updated answer with recursive CTE. It build hierarchy tree starting with records with new C3's value and display level as RowNo. 

with t as 
(select t.*, row_number () over (order by c2) rn from table1 t)

,temp (c2,c3,rn,lvl)  AS 
(SELECT c2,c3,rn,1 lvl from t t1  
                       where not exists(
                                        select 1 from t t0 
                                                 where t1.rn=t0.rn+1 
                                                   and t1.c3=t0.c3
                                       )
  UNION ALL 
 select t1.c2,t1.c3,t1.rn,lvl + 1 AS lvl FROM t t1
 join temp t2 on t1.rn=t2.rn+1 and t1.c3=t2.c3)

SELECT c2, c3, lvl rowno FROM temp order by rn;

http://sqlfiddle.com/#!3/4adbd/1

Upvotes: 1

Pravin Bakare
Pravin Bakare

Reputation: 174

SELECT ROW_NUMBER() over(PARTITION BY [C3] order by [C2]), * FROM table name

Upvotes: 0

Anton
Anton

Reputation: 9961

ROW_NUMBER() function can help you to set numbers of rows:

SELECT ROW_NUMBER() over(order by [some field]), *
      FROM [your table]

Upvotes: 0

Related Questions