• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

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
0
ScubeduFan
Asked:
ScubeduFan
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
ok got it , working. Good One Vitor :) and I totally take that back.

Regards,
Pawan
0
 
Pawan KumarDatabase 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     )



--

Open in new window


--

;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



--

Open in new window


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
 
ScubeduFanAuthor Commented:
Works great! Thank You!!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now