selecting top rows by counting a variable total

Question

Post reply

selecting top rows by counting a variable total

louise 28346

Valued Member

Points: 59
More actions
August 18, 2016 at 6:08 am

#150499

Hi
currently Ive been using set @rowcount to select the top subscribers in a list
Now I wish to select the records and stop when the amount of copies reaches a certain number
list
subscriberid copies
123 1
456 2
789 3
previously I use @rowcount, so if I said 3 I'd get these 3 subscribers
Now I want 3 copies
so I should get
123 1
456 2
Also what if I wanted 4 copies, so Id want
123 1
456 2
789 1 (1st of 3 copies)
returned?
Any ideas?
Cheers

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

-- set up some data to code against

DROP TABLE #MyTable

SELECT *

INTO #MyTable

FROM (

SELECT 123, 1 UNION ALL

SELECT 456, 2 UNION ALL

SELECT 789, 1 UNION ALL

SELECT 789, 2 UNION ALL

SELECT 789, 3

) d (subscriberid, copies);

-- here's a recursive CTE solution

;WITH SequencedData AS (

SELECT

rn = ROW_NUMBER() OVER(ORDER BY subscriberid),

*

FROM #MyTable

),

rCTE AS (

SELECT

*, SUMcopies = copies

FROM SequencedData

WHERE rn = 1 -- first or "anchor" row

UNION ALL

SELECT

nr.*, SUMcopies = (lr.SUMcopies + nr.copies)

FROM rCTE lr -- last row

INNER JOIN SequencedData nr -- next row

ON nr.rn = lr.rn+1

WHERE lr.SUMcopies + nr.copies <= 4

)

SELECT subscriberid, copies

FROM rCTE

OPTION(MAXRECURSION 0)

^{“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

louise 28346 Valued Member Points: 59 More actions · Answer 2

thanks Chris

Its this part

SELECT 123, 1 UNION ALL

SELECT 456, 2 UNION ALL

SELECT 789, 1 UNION ALL

SELECT 789, 2 UNION ALL

SELECT 789, 3

Ive an issue with because my data will come back as

123 1

456 2

789 3

I dont know how to get them into separate rows

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

louise 28346 (8/18/2016)
thanks Chris
Its this part
SELECT 123, 1 UNION ALL
SELECT 456, 2 UNION ALL
SELECT 789, 1 UNION ALL
SELECT 789, 2 UNION ALL
SELECT 789, 3
Ive an issue with because my data will come back as
123 1
456 2
789 3
I dont know how to get them into separate rows

That's just a mockup of your data, Louise - to have something for the data to run against. Substitute your tablename for #MyTable in the solution part.

^{“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

drew.allen SSC Guru Points: 76989 More actions · Answer 4

Are you aggregating your data in your query or is it pre-aggregated? That is, do you have two records for 456 and three records for 789, or do you have one record for each with the total number of copies? It makes a difference to the approach to take.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76989 More actions · Answer 5

This non-recursive CTE works with pre-aggregated (and mixed) data.

;

WITH running_total AS (

SELECT *, SUM(mt.copies) OVER(ORDER BY mt.subscriberid , mt.copies ROWS UNBOUNDED PRECEDING) AS rt

FROM #MyTable mt

)

SELECT rt.subscriberid,

CASE

WHEN rt.rt <= @num_records THEN rt.copies

WHEN rt.rt - rt.copies < @num_records THEN @num_records - rt.rt + rt.copies -- rt.rt - rt.copies is the previous running total.

ELSE 0

END AS copies

FROM running_total rt

WHERE rt.rt - rt.copies < @num_records

ORDER BY subscriberid, copies

The other version is even simpler, but it does require that you have a separate record for every copy.

;

WITH top_records AS (

SELECT TOP(@num_records) *

FROM #MyTable mt

ORDER BY mt.subscriberid, mt.copies -- or whatever you want your order to be.

)

SELECT mt.subscriberid, SUM(mt.copies) AS copies

FROM #MyTable mt

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76989 More actions · Answer 6

ChrisM@Work (8/18/2016)
-- set up some data to code against DROP TABLE #MyTable SELECT * INTO #MyTable FROM ( SELECT 123, 1 UNION ALL SELECT 456, 2 UNION ALL SELECT 789, 1 UNION ALL SELECT 789, 2 UNION ALL SELECT 789, 3 ) d (subscriberid, copies); -- here's a recursive CTE solution ;WITH SequencedData AS ( SELECT rn = ROW_NUMBER() OVER(ORDER BY subscriberid), * FROM #MyTable ), rCTE AS ( SELECT *, SUMcopies = copies FROM SequencedData WHERE rn = 1 -- first or "anchor" row UNION ALL SELECT nr.*, SUMcopies = (lr.SUMcopies + nr.copies) FROM rCTE lr -- last row INNER JOIN SequencedData nr -- next row ON nr.rn = lr.rn+1 WHERE lr.SUMcopies + nr.copies <= 4 ) SELECT subscriberid, copies FROM rCTE
OPTION(MAXRECURSION 0)

Chris, your sample data has 789 set up with six copies rather than 3. If you comment out the first two records for 789, your query doesn't work.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 7

drew.allen (8/18/2016)
ChrisM@Work (8/18/2016)
-- set up some data to code against DROP TABLE #MyTable SELECT * INTO #MyTable FROM ( SELECT 123, 1 UNION ALL SELECT 456, 2 UNION ALL SELECT 789, 1 UNION ALL SELECT 789, 2 UNION ALL SELECT 789, 3 ) d (subscriberid, copies); -- here's a recursive CTE solution ;WITH SequencedData AS ( SELECT rn = ROW_NUMBER() OVER(ORDER BY subscriberid), * FROM #MyTable ), rCTE AS ( SELECT *, SUMcopies = copies FROM SequencedData WHERE rn = 1 -- first or "anchor" row UNION ALL SELECT nr.*, SUMcopies = (lr.SUMcopies + nr.copies) FROM rCTE lr -- last row INNER JOIN SequencedData nr -- next row ON nr.rn = lr.rn+1 WHERE lr.SUMcopies + nr.copies <= 4 ) SELECT subscriberid, copies FROM rCTE
OPTION(MAXRECURSION 0)
Chris, your sample data has 789 set up with six copies rather than 3. If you comment out the first two records for 789, your query doesn't work.
Drew

The rules are still not clear Drew but I take your point, thanks.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

louise 28346 Valued Member Points: 59 More actions · Answer 8

Hi Chris and Allen

Yes the data is aggregated ie 1 record for a row where the copies are 2,3 etc

Ive tried

declare @num_records int

set @num_records = 4

SELECT *

INTO #MyTable

FROM (

SELECT 123, 1 UNION ALL

SELECT 456, 2 UNION ALL

SELECT 789, 4

) d (subscriberid, copies);

;

WITH running_total AS (

SELECT *, SUM(mt.copies) OVER(ORDER BY subscriberid , mt.copies ROWS UNBOUNDED PRECEDING) AS rt

FROM #MyTable mt

)

SELECT rt.subscriberid,

CASE

WHEN rt.rt <= @num_records THEN rt.copies

WHEN rt.rt - rt.copies < @num_records THEN @num_records - rt.rt + rt.copies -- rt.rt - rt.copies is the previous running total.

ELSE 0

END AS copies

FROM running_total rt

WHERE rt.rt - rt.copies < @num_records

ORDER BY subscriberid, copies

and get the error 'Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'ROWS'.'

drew.allen SSC Guru Points: 76989 More actions · Answer 9

Sorry, I didn't realize that you were on SQL 2005. You need SQL 2012 to use this construct. I should pay more attention to which forum I'm in.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA