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

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

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

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: