Get Records depend on their sum value

Question

I have a SQL Server table which have records like this

ID | Value

1   |   100
2   |   150
3   |   250
4   |   600
5   |   1550
6   |   50
7   |   300

I need to select random records, but the only condition is that the total sum of this records value achieve a specific number or percentage i define.

let's say i need a total value of 300 or 10%, so here are the chances

1   |   100
2   |   150
6   |   50

or

3   |   250
6   |   50

or

7   |   300

can any one help me to do this.

Answer 1

Think this recursive CTE works, no idea what the performance will be like though once you get past a trivial amount of rows:

DECLARE @Test TABLE
(
    ID INT NOT NULL,
    VAL INT NOT NULL
);

INSERT INTO @Test
VALUES  (1,100),
        (2,150),
        (3,250),
        (4,600),
        (5,1550),
        (6,50),
        (7,300);

DECLARE @SumValue INT = 300,
        @Percentage INT = 10;

WITH GetSums
AS
(
    SELECT  T.ID, 
            T.Val,
            CAST(T.ID AS VARCHAR(MAX)) AS IDs
    FROM    @Test AS T

    UNION ALL

    SELECT  T1.ID, 
            T1.Val + GS.Val AS Val,
            CAST(T1.ID AS VARCHAR(MAX)) + ',' + GS.IDs AS IDs
    FROM    @Test AS T1
    INNER
    JOIN    GetSums AS GS 
            ON  T1.ID > GS.ID
)
SELECT  GS.IDs,
        GS.Val
FROM    GetSums AS GS
WHERE   (GS.Val = @SumValue OR GS.VAL = (SELECT SUM(Val) FROM @Test AS T) / @Percentage)
OPTION  (MAXRECURSION 50);

Similar found here:

find all combination where Total sum is around a number