Ranking rows on basis of SUM of records

Question

Post reply

Ranking rows on basis of SUM of records

rhd110

Ten Centuries

Points: 1062
More actions
July 17, 2014 at 4:20 am

#288682

Hi Team,
Following is my table structure
IDRowCount PagID
1448
2267
3297
4216
5405
6254
7107
8102
9218
10432
1158
128
13239
14291
15201
16240
17219
18479
1996
20164
PageId is currently set to 0
I have a user input, @IntNoOfRowsPerPage = 800 Means 800 rows per page. So following is the output I require.
AIDRowCountPageId
1448 1
2267 1
3297 2
4216 2
5405 3
6254 3
7107 3
8102 4
9218 4
10432 4
1158 5
128 5
13239 5
14291 5
15201 5
16240 6
17219 6
18479 7
1996 7
20164 7
The values of PageID are such that summation of RowCount for PageID is <= @IntNoOfRowsPerPage (i.e, 800)
Please can some one provide me optimized solution for the same.
Also let me know if NTILE function can be used in such scenarios.
Regards

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rhd110 Ten Centuries Points: 1062 More actions · Answer 1

rhd110

Ten Centuries

Points: 1062

July 17, 2014 at 6:57 am

#1730230

Hi Techies any reply for this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

rhd110 (7/17/2014)
Hi Techies any reply for this.

DROP TABLE #Temp

CREATE TABLE #Temp (ID int, [RowCount] int)

INSERT INTO #Temp (ID, [RowCount]) VALUES

(1, 448),(2, 267),(3, 297),(4, 216),(5, 405),(6, 254),(7, 107),(8, 102),(9, 218),(10, 432),

(11, 58),(12, 8),(13, 239),(14, 291),(15, 201),(16, 240),(17, 219),(18, 479),(19, 96),(20, 164)

;WITH rCTE AS (

SELECT tr.ID, tr.[RowCount],

rt = tr.[RowCount],

[Page] = 1

FROM #Temp tr

WHERE tr.ID = 1

UNION ALL

SELECT tr.ID, tr.[RowCount],

rt = CASE WHEN tr.[RowCount] + lr.rt > 800 THEN tr.[RowCount] ELSE tr.[RowCount] + lr.rt END,

[Page] = CASE WHEN tr.[RowCount] + lr.rt > 800 THEN lr.[Page] + 1 ELSE lr.[Page] END

FROM #Temp tr

INNER JOIN rCTE lr

ON lr.ID + 1 = tr.ID

)

SELECT ID, [RowCount], [Page]

FROM rCTE

ORDER BY ID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

rhd110 (7/17/2014)
Hi Techies any reply for this.

Yep, but haven't heard anything since.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rhd110 Ten Centuries Points: 1062 More actions · Answer 4

Thanks man this works. Recursive cte helped. And my problem is solved in this case.

Just out of anxiety a question, can't we use NTILE in such cases ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Not really. NTILE(n) splits a set into n partitions.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden