T/SQL - how to select first 10 (or less) rows with maximum total sum

Question

I need to select 10 or less rows with the SUM(FileSize) < 1000000. Results need to be ordered by AttachmentId. Let's say that if a single FileSize exceeds the limit it's okay just for that row (and no other) to be selected.

Ideally I'd like it to be just a select query with no more statements.

The table is:

CREATE TABLE [Attachment](
    [AttachmentId] [int] NOT NULL,
    [FileSize] [int] NOT NULL
)

Please help.

Updated. Sorry to hear that the requirements are unclear for most of the readers. There is no requirement to do any grouping. All I need to get is just plain first 10 rows or less. It will be less than 10 if their total on FileSize exceeds 1000000. It will be only 1 row if its FileSize equals 1000000 or more. The server is SQL 2008.

Updated. Many thanks to Nikola. We are getting there, but I'm still not sure how to implement the case when the first row exceeds FileSize of 1000000.

SELECT TOP 10 a.AttachmentId, rt.runningTotal 
FROM Attachment a
CROSS APPLY (SELECT SUM(aa.FileSize) AS runningTotal
   FROM Attachment aa
   WHERE aa.AttachmentId <= a.AttachmentId
) AS rt
GROUP BY a.AttachmentId, rt.runningTotal
HAVING rt.runningTotal < 1000000
ORDER BY a.AttachmentId

Solution. This is the code (slightly modified) from Stuart which I accept as answer. Many thanks to Stuart!:

WITH CTE
  AS ( SELECT TOP 10 AttachmentId, FileSize
, RunningID = ROW_NUMBER() OVER (ORDER BY AttachmentId)
   FROM Attachment
  )
SELECT AttachmentId, FileSize
FROM CTE AS a
WHERE (SELECT SUM(FileSize)
       FROM CTE
       WHERE RunningID <= a.RunningID
       ) <= 10000000
       OR a.RunningID = 1

Do [running sum](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) on rows ordered by FileSize desc, and add having which will limit running sum to number less than 1000000. Union that with row having max(FileSize) to cover the case when one row exceeds the limit. — Nikola Markovinović, Aug 30 '12 at 10:21
To t-clausen.dk: There is no grouping as such. Sorry if my post was not clear enough. — evgeny, Aug 31 '12 at 00:18
To Nikola: The first part of your suggestion is exactly what I need, thanks a lot. The second part on "to cover the case when one row exceeds the limit" is confusing me - could you elaborate a little more on this? — evgeny, Aug 31 '12 at 01:33
I meant first row having filesize > maximum, what Stuart Ainsworth does with `OR A.RunningID = 1`. Sorry for being vague. Oh, and when you want to contact someone other than post owner be sure to prefix the name with @. This will send notification to user. — Nikola Markovinović, Aug 31 '12 at 09:45

Stuart Ainsworth · Accepted Answer · 2012-08-31T02:17:25.493

2

Maybe this will get you started:

BEGIN TRAN

CREATE TABLE [Attachment]
    (
      [AttachmentId] [int] NOT NULL
    , [FileSize] [int] NOT NULL
    ) 


INSERT  INTO Attachment
        SELECT  1
              , 10
        UNION
        SELECT  2
              , 20
        UNION
        SELECT  3
              , 30

--values to exceed
DECLARE @p INT = 50

--row count to restrict to
DECLARE @r INT = 10

;
WITH    CTE
          AS ( SELECT   AttachmentID
                      , FileSize
                      , RunningID = ROW_NUMBER() OVER ( ORDER BY FileSize DESC )
               FROM     Attachment
             )
    SELECT TOP ( @r )
            AttachmentID
          , FileSize
    FROM    CTE AS A
    WHERE   ( SELECT    SUM(FileSize)
              FROM      CTE
              WHERE     RunningID <= A.RunningID
            ) <= @p 
OR A.RunningID = 1



ROLLBACK

edited Aug 31 '12 at 02:17

answered Aug 31 '12 at 01:30

Stuart Ainsworth

12,792
41
46

Thanks Stuart, a great idea with RunningIDs ordered by FileSize, but in terms of the result it's similar to Nikola's idea with CROSS APPLY (I have updated my question with the code which is working fine). – evgeny Aug 31 '12 at 01:59
Modified my answer to account for that use case. – Stuart Ainsworth Aug 31 '12 at 02:18
Thanks Stuart, very elegant indeed. :-) – evgeny Aug 31 '12 at 03:12

score 1 · Answer 2 · answered Aug 30 '12 at 08:44

1

SELECT TOP 10 AttachmentId
FROM Attachment
GROUP BY AttachmentId
HAVING SUM(FileSize) < 1000000
ORDER BY AttachmentId

answered Aug 30 '12 at 08:44

aF.

64,980
43
135
198

Thanks aF. It would have been great if it was that easy. You are grouping by AttachmentId therefore HAVING is applicable to just 1 row. What this query does is it selects first 10 rows where FileSize < 1000000. It is equivalent to: `SELECT TOP 10 AttachmentId FROM Attachment WHERE FileSize < 1000000 ORDER BY AttachmentId` – evgeny Aug 31 '12 at 00:10
@evgeny ok, I didn't understood then ;) – aF. Aug 31 '12 at 09:00

T/SQL - how to select first 10 (or less) rows with maximum total sum

2 Answers2