# Order Rows According to Score Column– A Solution

```
Create table test (Id int, Name varchar(50), Score int)
Insert into test values
(1,char(65),2),(2,char(66),1), (3,char(67),1),(4,char(68),1), (5,char(69),2),(6,char(70),2)
, (7,char(71),2),(8,char(72),1),(9,char(73),1),(10,char(74),1), (11,char(75),2)
,(12,char(76),2),(13,char(77),2),(14,char(78),1),(15,char(79),1)

;with mycte as
(select id, Name, score,row_number() Over(Partition by score Order By id) rn  from test)

,mycte1 as (Select id, Name, score, row_number() Over(Order by  rn,score ) rn2 from mycte)

,
myfinal
as
(
select id, Name, rn2, score, score as running_total
from mycte1
where rn2 = 1
union all
select  m.id, m.Name,m.rn2, m.score, m.score + r.running_total as running_total
from myfinal as r inner join mycte1 as m on m.rn2 = r.rn2 + 1
)
SELECT id, Name, score,  CASE WHEN running_total%6=0 Then (running_total)/6 -1 Else (running_total)/6 End  as Sortord from myfinal m
Order By Sortord,rn2
--option (maxrecursion 0)

drop table test
--http://forums.asp.net/t/1959158.aspx?arrange+records+according+score
```