To generate sequential numbers on the fly using inner join

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 211
Suppose we have a table as under

Declare @t Table(Name Varchar(20),Age int)
Insert Into @t
Select 'Name1', 20 Union All Select 'Name2', 21 Union All
Select 'Name3', 22 Union All Select 'Name4', 23 Union All
Select 'Name5', 24


Select
Rn = COUNT(*)
,t1.Name
,t1.Age
From @t t1
Join @t t2 On t1.Name >= t2.Name
Group By t1.Name,t1.Age
Order By t1.Name,t1.Age


/* Result */
Rn Name Age
1 Name1 20
2 Name2 21
3 Name3 22
4 Name4 23
5 Name5 24

Comments or Responses

Login to post response