Using Co-Related Subquery and an Aggregate Function to generate sequential numbers on the fly

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 161
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 
(
Select Count(*)
From @t t2
Where t2.Name <= t1.Name
And t2.Age <= t1.Age
) As Rn
,t1.Name
,t1.Age
From @t t1


OR

Select 
(
Select Max(1)
From @t t2
Where t2.Name <= t1.Name
And t2.Age <= t1.Age
) As Rn
,t1.Name
,t1.Age
From @t t1


OR

Select 
(
Select Sum(1)
From @t t2
Where t2.Name <= t1.Name
And t2.Age <= t1.Age
) As Rn
,t1.Name
,t1.Age
From @t t1


/* 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