# Ranking Based On Value

I know how to do basic ranking, but is it possible to do it based on a sum of a value?

Using the small subset of data below, my goal is to sum up the "DescValue" column and every SUM of 5, I would increment the "Page" number. The "Page" must increment to never allow the sum of "DescValue" on a page to be greater than 5.

I am using MS SQL 2014.

Description            DescValue      Page
Line1                   1                  1
Line2                   2                  1
Line3                  1                  1
Line4                  2                  2
Line5                  3                  2
Line6                  2                  3
Line7                  4                  4
Line8                  1                  4
Line9                  1                  5
###### Who is Participating?

x

Database ExpertCommented:
Here is the solution, try

Table creation

``````--

Create table five
(
Description varchar(100)
,DescValue  int
)
GO

INSERT INTO five VALUES
('Line1'            ,       1   ),
('Line2'            ,       2     ),
('Line3'            ,      1     ),
('Line4'            ,      2     ),
('Line5'             ,     3     ),
('Line6'             ,     2     ),
('Line7'            ,      4      ),
('Line8'            ,      1     ),
('Line9'           ,       1     )

--
``````

``````--

;WITH CTE As
(
SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM Five
)
,CTE1 AS
(
SELECT Description , DescValue a1 , DescValue , rnk , 1 Lvl FROM CTE WHERE rnk = 1
UNION ALL
SELECT c1.Description , c1.DescValue a1,
CASE WHEN c1.DescValue + c.DescValue > 5 THEN c1.DescValue ELSE c1.DescValue + c.DescValue END DescValue , c1.rnk
,CASE WHEN c1.DescValue + c.DescValue > 5 THEN Lvl + 1 ELSE Lvl END Lvl
FROM CTE c1 INNER JOIN CTE1 c ON c.rnk + 1 = c1.rnk
)
SELECT Description , DescValue , lvl Page FROM CTE1
OPTION (MAXRECURSION 0)

GO

--
``````

Output
------------------
Description      DescValue      Page
Line1               1                       1
Line2               3                       1
Line3               4                       1
Line4               2                       2
Line5               5                       2
Line6               2                       3
Line7               4                       4
Line8               5                       4
Line9               1                       5

Hope it helps !!
0

Database ExpertCommented:
ok got it , working. Good One Vitor :) and I totally take that back.

Regards,
Pawan
0

Author Commented:
Works great! Thank You!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.