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
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s