Select random rows till the sum of column reaches the target in sql server

Question

I need the random rows to be displayed till the target reaches it should reach the target if there is any possible combination is there.

    Id  price 
    1    20
    2    30
    3    40
    4    15
    5    10
.......200+ rows

If the target is 30 it should return with

id   price
1     20
5     10

or

id   price
2     30

If the target exceeds the sum or no combinations --120 in this case it should return till last maximum

Id  price 
1    20
2    30
3    40
4    15
5    10

If the target is less than the sum --5 in this case it should return nothing

Id  price

what i tried

select t.*(select q.*,sum(q.price) over(order by newid())as total from Orders q)t
where t.total<=45

i want rows till the exact target is reached if there is no possible combinations of getting the exact target then only it has to return till the nearest value to the target

this query is giving me the rows not exactly all the time

Answer 1

I aggre with @siggemannen that you should do this for very small populations. Anyway is a nice excercise, here I'm addressing the 4 scenarios.

• If SUM(Price) < TargetPrice return all
• IF MIN(Price) < TargetPrice return nothing
• Calculate all posible combinations of possible SUM(Price) between different rows. IF there is one combination equals to TargetPrice THEN return it. ELSE return all

So the code look like:

DECLARE @table TABLE (ID INT, Price money )--DECIMAL(10,4))
INSERT INTO @table (ID, Price) VALUES
(1, 20), (2, 30), (3, 40), (4, 15), (5, 10)
DECLARE @TargetPrice Money = 30
IF @TargetPrice > (SELECT SUM(Price) FROM @table) --If the target exceeds the sum
BEGIN
    SELECT * 
    FROM @table
END
ELSE IF @TargetPrice < (SELECT MIN(Price) FROM @table) --If the target is less than the sum
BEGIN
    SELECT TOP 0 *
    FROM @table
END
ELSE 
BEGIN
    ;WITH cte AS ( -- find all possible combinations recursively 
        SELECT id, Price ,  CONVERT(VARCHAR(MAX),Id) IdLst
        FROM @table
        UNION ALL 
        SELECT t.id, cte.Price + t.Price  as Price, cte.IdLst + '|' + CONVERT(VARCHAR(MAX),t.Id)   IDLst
        FROM cte 
        INNER JOIN @table t 
            on cte.ID < t.ID
    ), EqCombinations AS (  -- keep only the first one that matches the target price 
        SELECT *, ROW_NUMBER() OVER (ORDER BY IDLst)  rn
        FROM cte
        WHERE cte.Price = @TargetPrice 
    ), Eq AS (  -- pick the first one
        SELECT t.* 
        FROM EqCombinations
        CROSS APPLY (SELECT * FROM STRING_SPLIT(IDLst, '|') parts) p
        INNER JOIN @table t
            ON p.value = t.ID
        WHERE rn = 1
    )
    SELECT * , 'eq' why FROM Eq  -- if the target was matched
    -- else return all
    UNION ALL SELECT * , 'all' why FROM @table WHERE NOT EXISTS(SELECT 1 FROM Eq)
END

Answer 2

I took your plain text data and turned it into reproducible DDL/DML:

DECLARE @table TABLE (ID INT, Price DECIMAL(10,4))
INSERT INTO @table (ID, Price) VALUES
(1, 20), (2, 30), (3, 40), (4, 15), (5, 10)

It's really helpful to have this in your question.

This was an interesting challenge to solve, I'm not sure I interpreted your requirement exactly, but here's what I understood:

Select a random row, check the new running total against a value, and if it's less get another random (but distinct!) row and check again. Repeat until the first row which puts the running total over the check value.

;WITH MinMax AS (
SELECT MIN(ID) AS minID, MAX(ID) AS maxID
  FROM @table
)

SELECT ID, Price    
  FROM (
        SELECT a.ID, a.Price, a.rn, SUM(Price) OVER (ORDER BY rn) AS rTotal
          FROM (
                SELECT t.ID, t.Price, ROW_NUMBER() OVER (ORDER BY RAND(CONVERT(VARBINARY,NEWID(),1))) AS rn
                  FROM (VALUES (
                                RAND(CONVERT(VARBINARY,NEWID(),1))
                               )
                       ) A(Rnd1)
                    CROSS APPLY (SELECT minID, maxID FROM MinMax) mm
                    CROSS APPLY (SELECT TOP 100 1 AS z FROM sys.system_objects a CROSS APPLY sys.system_objects) z
                    LEFT OUTER JOIN @table t
                      ON ROUND(((maxID - minID) * Rnd1 + minID), 0) = t.ID
                 GROUP BY t.ID, t.Price
               ) a
       ) a
 WHERE rTotal < = 50

So here we use some voodoo to find rows, and put them into a random order. Next we apply the windowed function ROW_NUMBER to give is a column to preserve that order. Next we can use the windowed function SUM to get a running total, of those rows, in that random order and finally we can compare the running total of each row, in that order, to the check value.

Example output:

ID	Price
2	30.0000
4	15.0000

ID	Price
5	10.0000
1	20.0000
4	15.0000