Solved

Ranking Based On Value

Posted on 2016-11-10
3
45 Views
Last Modified: 2016-11-13
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
Comment
Question by:ScubeduFan
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41883326
ok got it , working. Good One Vitor :) and I totally take that back.

Regards,
Pawan
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41883678
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
 

Author Closing Comment

by:ScubeduFan
ID: 41885385
Works great! Thank You!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question